Mysql Left join with condition on column

Question

Hi, can you help me with sql query? I have this problem: I have two tables

"Join" table: Reservation_has_meal

+----------------+
| id_reservation |
| id_meal        |
| pieces         |
+----------------+

and table with data: Meal

+-------------+
| id_meal     |
| name        |
+-------------+

Sample data for

Meal:
1 | carrot   
2 | potatoe  
3 | cucumber 

Reservation_has_meal
1 | 2 | 5230
1 | 3 | 1203

How can I get this result for reservation with id_reservation=1:

id_meal | id_Reservation | name      | pcs |
--------------------------------------------
1       | 1              | carrot    | null|
2       | 1              | potatoe   | 5230|
3       | 1              | cucumber  | 1203|
--------------------------------------------

And result for id_reservation = 2:

id_meal | id_Reservation | name      | pcs |
--------------------------------------------
1       | 2              | carrot    | null|
2       | 2              | potatoe   | null|
3       | 2              | cucumber  | null|
--------------------------------------------

Thanks for advice.

Answer 1

check this out

for id_reservation = 1

select ml.id_meal  as id_meal,id_Reservation,name,pcs  from  Meal as ml
left outer join 
(select IFNULL(id_Reservation,1) as  id_Reservation, pieces as pcs,id_meal from    Reservation_has_meal    where id_reservation=1) rm
on  rm.id_meal  =  ml.id_meal

for id_reservation = 2

select ml.id_meal  as id_meal,id_Reservation,name,pcs  from  Meal as ml
left outer join 
(select IFNULL(id_Reservation,2) as  id_Reservation, pieces as pcs,id_meal from    Reservation_has_meal    where id_reservation=2) rm
on  rm.id_meal  =  ml.id_meal

i.e you require to replace id_reservation value with the value you are using for searching

Answer 2

I have some solution, but I don't want to use inner select and if I would have more data in tables the query is very ugly.

SELECT *, (SELECT pcs FROM Reservation_has_meal WHERE Reservation_has_meal.id_meal = meal.id_meal AND Reservation_has_meal.id_reservation=1) FROM meal