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.
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?