1

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.

0
1

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'
2
  • Thanks so much McNets, it worked perfectly. It seems so obvious now in hindsight. Jan 16 '17 at 0:05
  • I'm glad to be able to help.
    – McNets
    Jan 16 '17 at 0:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.