How to put data from multiple rows into one row array in MySQL

Question

Suppose I have a table days which specifies the breakfast food and dinner food for each day, using IDs that point to a table food which contains the food names. How do I fetch the breakfast and dinner food names using a single query? Basically I want to combine the following into one query:

SELECT days.*, food.name AS breakfast_name FROM days, food WHERE days.breakfast_id = food.id AND days.id = '12'
SELECT days.*, food.name AS dinner_name FROM days, food WHERE days.dinner_id = food.id AND days.id = '12'

A properly normalized data structure would make this a lot easier. That is you'd have a one-to-many join table between days and food with some kind of type column that can be used for grouping like this. You've violated the Zero, One or Infinity Rule by having two join columns. — tadman
– tadman, Commented Oct 17, 2014 at 21:16
Can you give the structure and data samples from both tables? It may help get a better understanding of what you need. Also, how many columns do you expect in the combined query? I'm assuming you're not only looking to do a simple union. — Jerome
– Jerome, Commented Oct 17, 2014 at 21:19

hukir · Accepted Answer · 2014-10-17 22:29:07Z

1

You can do it in a single query by joining the food table twice on the separate ids.

SELECT days.*, bf.name AS breakfast_name, df.name AS dinner_name FROM days JOIN food AS bf ON days.breakfast_id=bf.id JOIN food AS df ON days.dinner_id=df.id;

answered Oct 17, 2014 at 22:29

hukir

1,7531 gold badge9 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to put data from multiple rows into one row array in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related