Mysql Left join with condition on column
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 data table: Power
+ ------------- + | 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 a reservation with id_reservation = 1:
id_meal | id_Reservation | name | pcs | -------------------------------------------- 1 | 1 | carrot | null | 2 | 1 | potatoe | 5230 | 3 | 1 | cucumber | 1203 | --------------------------------------------
And the 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 the advice.
+2
a source to share
3 answers
check it
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
ie you need to replace the id_reservation value with the value you are using to search
+2
a source to share
I have some solution, but I don't want to use an internal selection and if I had more data in the tables the query would be 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
0
a source to share