SQL query using an adjacent field value as a criterium

Question

3 tables in a database. Table1 is tabCategory, Table2 is tabSub, Table3 is Parts.
Table3, Parts, is populated with 3 columns, multiple entries, thousands of them. Column1 is the part number, column2 is the Category and column3 is the Type from tabSub.
The user must fill column1 (PartNumber) according to the part number it wants to add in the DB. He must also enter the Category (PartType) for his part, wich is limited to a list form Table1, tabCategory. He must also enter the SubCategory limited by a list from Table2, tabSub. Table2 is built with some 40 subcategories to which correspond only 10 different categories.
For example: part: DC23, Category:Capacitor, SubCategory:Mylar...if the part was a Transistor the Catgory would be a Semiconductor and the SubCategory could not be Mylar.
The Challenge: How can I make an SQL query that would deliver a list of all the available SubCategory using as a criteria the adjacent field Category chosen by the user?

Something like this (which does not work):
SELECT tabSub.Category, Amplifier.Category FROM tabSub INNER JOIN Amplifier ON tabSub.Type = Amplifier.PartType WHERE (((tabSub.Category)=[Amplifier].[Category]));
The goal being to limit the available user choices based on his entry in the Category field for that particular record. This Query result would be used to limit his list of choices.
Thanks for your suggestions. Cheers

Answer 1

You just need to make your tables have the proper Foreign Key Constraints

CREATE TABLE categories
(
 /* The category name is actually its natural primary key */
 category CHARACTER(100) PRIMARY KEY
) ;
CREATE TABLE subcategories
(
 /* All subcategories have a 'parent' category and a 'subcategory' itself */
 /* REFERENCES is one way to define a Foreign Key constraint */
 category CHARACTER(100) NOT NULL REFERENCES categories(category),
 subcategory CHARACTER(100) NOT NULL,
 /* The primary key is actually the 2 columns */
 PRIMARY KEY (category, subcategory)
) ;
CREATE TABLE parts
(
 part_number CHARACTER(10) PRIMARY KEY,
 category CHARACTER(100),
 subcategory CHARACTER(100),
 part CHARACTER VARYING (255),
 /* The pair (category, subcategory) must appear on the 'subcategories' table */
 /* This is actually a second way of defining a Foreign Key Constraint */
 CONSTRAINT 
 FOREIGN KEY (category, subcategory) 
 REFERENCES subcategories (category, subcategory)
) ;

Some sample data:

INSERT INTO 
 categories (category) 
VALUES 
 ('Capacitor'), 
 ('Resistor') ;
INSERT INTO 
 subcategories (category, subcategory)
VALUES
 ('Capacitor', 'Mylar'),
 ('Capacitor', 'Ceramic'),
 ('Resistor', 'Carbon'),
 ('Resistor', 'Film'),
 ('Resistor', 'Mylar' /* if it ever made sense */) ;
INSERT INTO
 parts
 (part_number, category, subcategory, part)
VALUES
 ('DC23', 'Capacitor', 'Mylar', 'High voltage capacitor'),
 ('DC24', 'Capacitor', 'Ceramic', 'Higher voltage capacitor') ;

You can check it at SQLFiddle

The SQL that gets the list of all possible subcategories, given one category, is straightforward:

SELECT 
 subcategory 
FROM 
 subcategories 
WHERE 
 category = 'Resistor' 
ORDER BY
 subcategory ;

• Fred Cailloux: As much as I try I cannot get my SQL interpreter to swallow anything else then this: SELECT tabSub.lngSubIndex, tabSub.txtSubType FROM tabSub INNER JOIN Amplifier ON tabSub.lngSubIndex = Amplifier.lngAmpSub WHERE ((tabSub.lngSubIndex)=(Amplifier.lngAmpSub)) AND ((tabSub.lngCategoryFROMtabCat)=(Amplifier.lngAmpCategory))
• Fred Cailloux: The example you proposed on SQLFiddle gave me some insight as to who to implement the logic but since I am using MS_ACCESS 2007 it doesn't seem to work as you expected. Also, in the last example when you refer to the word 'Resistor' in the WHERE line, we have to keep in mind that this variable must come from inside the Amplifier table column lngAmpCategory which is the column that contain the actual value selected by the user.
• Fred Cailloux: The difficulty I have is to grab that value from that particular column.vs.field instead of plugging one single value into the SQL code, which, by the way, has worked fine when I tested it. Appreciate your help.
• joanolo: @FredCailloux: you tagged your question with mysql, not with access. And you didn't specify your Amplifier table in your question (you talked about categories, sub[categories] and parts. It is difficult to answer a moving question.
• joanolo: Check How do I ask a good question?.