SQL selecting multiple rows from same column based on different column values

10 pts.
Tags:
MySQL query
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
object_typeobject_restriction_IDobject_restriction
protein5003ham
carbs5015apple
fats5020cheese
vitamin5030supplement
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following