Innodb – I have 2 parent tables with 1 child and I want to perform a query that involves both parents

innodbmariadb

I have one table that contains information about different types of hops for use in brewing and a second that contains a list of aromas used to describe hops. I've made a third table that contains a primary key and 2 foreign keys (one is the primary key of the hops table and one is the primary key of the aromas table). I set it up as two tables because any hops variety may have several aroma descriptors and any aroma may apply to several hops varieties.

The child of the two parents was created in the following way:

CREATE TABLE IF NOT EXISTS aromareference 
( ID int(5) NOT NULL AUTO_INCREMENT,
  HopsID int(5) DEFAULT NULL, 
  DescriptorID int(5) DEFAULT NULL, 
  CONSTRAINT FOREIGN KEY (HopsID) 
    REFERENCES hops (ProductID), 
  CONSTRAINT FOREIGN KEY (DescriptorID) 
    REFERENCES hopsdescriptors (DescriptorID), 
  PRIMARY KEY(ID)
);

The hops table has a column called HumanName, as does the hopsdescriptors table. I would like to perform a query using the HumanName from the hops table to list from the hopsdescriptors table all of the applicable HumanNames of the aromas.

As an example output, I would be looking for something like:

+------------+
| Centennial |
+------------+
| Floral     |
| Citrus     |
+------------+

The basic structure of the tables in question is as follows:

CREATE TABLE IF NOT EXISTS hops 
('ProductID' int(5) NOT NULL AUTO_INCREMENT,
'HumanName' varchar(50) DEFAULT NULL,
'ShortDescript' varchar(500) DEFAULT NULL,
'PrimaryUse' int(1) DEFAULT NULL,
'AlphaAcidMin' float(5,2) DEFAULT NULL,
'AlphaAcidMax' float(5,2) DEFAULT NULL,
'CohumuloneMin' int(3) DEFAULT NULL,
'CohumuloneMax' int(3) DEFAULT NULL,
PRIMARY KEY ('ProductID')
);

CREATE TABLE IF NOT EXISTS hopsdescriptors
('DescriptorID' int(5) NOT NULL AUTO_INCREMENT,
'HumanName' varchar(20) DEFAULT NULL,
'ShortDescript' varchar(500) DEFAULT NULL,
PRIMARY KEY ('DescriptorID')
);

I imagine this is a pretty common query to perform, however I'm brand new to this stuff and I haven't even been able to think about the question clearly enough to google it.

Best Answer

Use aromareference table to fetch all referenced records from both tables, hops and hopsdescriptors.

Then filter records by hops.HumanName (or whatever field of hops or hopsdecriptors), and show the required columns.

SELECT hd.HumanName
FROM aromareference a
     INNER JOIN hops h ON a.HopsId = h.ProductId
     INNER JOIN hopsdescriptors hd ON a.DescriptorID = hd.DescriptorID
WHERE h.HumanName = 'Centennial'