SQL selecting multiple rows from same column based on different column values
I have am joining a table (object_restrictions) that has three columns: (object_type), (object_restriction_ID) and (object_restriction).
I would like to join the table on (object_restriction_id) but select multiple rows based on the value in the column (object_type) and have them listed as separate columns in the final query.
For example
'object_restrictions' table
I want to select the object_restrictions but join them at specific points based on the restriction ID. My main query would select the (object_restriction_ID)s based on parameters from a different table, and then next to that selected column I wanted to put the matching (object_restriction) data. So assuming that the joins for tables A and B are previously defined with values selected the rest of the query would look like:
select a.fad_diet_ID, b.food_group_ID, c. object_restriction AS Fad Diet Restriction, d. object_restriction AS Food Group Restriction
from diets a
join food_groups b on a.fad_diet_ID = b.fad_diet_ID
join object_restrictions c on a.fad_diet_ID = b.object_restriction_ID
join object_restrictions d on b.food_group_ID = b.object_restriction_ID
WHERE c.object_restrictions.object_type = 'protein'
OR d.object_restrictions.object_type = 'carbs'
Apparently when I was running this in MySQL it didn't like the multiple joins on the same table (object_restrictions) and kept running until I removed the second reference. Anyway I can still return the different object_restriction data points depending on the corresponding object_restriction ID where the object type is defined?
Software/Hardware used:
Toad
| object_type | object_restriction_ID | object_restriction |
| protein | 5003 | ham |
| carbs | 5015 | apple |
| fats | 5020 | cheese |
| vitamin | 5030 | supplement |
Software/Hardware used:
Toad
ASKED:
September 15, 2012 5:12 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
TomLiotta 
108,300 pts. ,
recovery123 275 pts.
108,300 pts. ,
recovery123 275 pts. To see all answers submitted to the Answer Wiki: View Answer History.
At the least, you’ll need to get your correlatives aligned. Your first JOIN is like this:
diets a join food_groups b on a.fad_diet_ID = b.fad_diet_ID
To simplify, I’ll call that Join1. That is then JOINed this way:
(Join1) JOIN object_restrictions c on a.fad_diet_ID = b.object_restriction_ID
You create a correlative “C” and assign it to object_restrictions, but nowhere in your ON clause is there any reference to a column from C. You only reference columns from A and B. How can you JOIN to C if you don’t tell SQL what column to JOIN ON?
Tom