userID
date
lunch
dinner

and this is my food table:

id 
food_name

I save id of food in lunch and dinner of menu table
I want to get lunch name and dinner name from food_name table.
I can get one of columns name by using LEFT JOIN like this:

SELECT m.user_id,
       m.date,
       m.lunch,
       m.dinner,
       f.id,
       f.food_name
FROM 
       menu m 
LEFT JOIN foods f ON
       m.lunch = f.id;

But the above code just loads lunch name. What should I do to get lunch and dinner name in the same query?
Thanks

CodePudding user response：

You could try:

SELECT m.user_id,
       m.date,
       m.lunch,
       m.dinner,
       f.id AS lunch_id,
       f.food_name AS lunch_name,
       g.id AS dinner_id,
       g.food_name AS dinner_name
FROM 
       menu m 
LEFT JOIN foods f ON
       m.lunch = f.id
LEFT JOIN foods g ON
       m.dinner = g.id;