SQL query using an adjacent field value as a criterium
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 ;