Where clause with multi AND & OR conditions-CodePudding

I got a table agenda in which the admin can make a reservation for him self or for someone else (another user). If the admin make the reservation for him self in agenda.user_id will be stored the id of admin.

In case that admin make a reservation for another person (another user) in agenda.user_id will be stored the id of the user for which the reservation will be made. The id of the admin will be stored in another column agenda.booked_user.

All the reservations are stored on agenda_users table also. agenda_users has this columns: id,agenda_id, user_id. The agenda_users.user_id it refers to agenda.user_id.

I want to retrieve all the reservations made by the admin which has made reservations for himself and for other users also.

I did a query with some AND & OR:

SELECT agenda.* 
FROM agenda,agenda_users 
WHERE agenda_users.agenda_id=agenda.id 
AND (agenda_users.user_id=$user_id 
AND agenda_users.user_id=agenda.user_id) 
OR (agenda_users.user_id=agenda.user_id 
AND agenda.booked_user=agenda.$user_id) 
AND checkout IS NULL 
AND NOW() < DATE_ADD(date_end, INTERVAL 6 HOUR) ORDER BY type ASC,date_start ASC

Cannot figure out the right solution to 'grab' all the reservations the admin has made for him self and other users.

CodePudding user response：

solving the old-style-joins will leave you with this SQL:

SELECT agenda.* 
FROM agenda
INNER JOIN agenda_users ON agenda_users.user_id=agenda.user_id AND agenda_users.agenda_id=agenda.id
WHERE 
  (agenda_users.user_id=$user_id) OR (agenda.booked_user=agenda.$user_id) 
  AND checkout IS NULL 
  AND NOW() < DATE_ADD(date_end, INTERVAL 6 HOUR) ORDER BY type ASC,date_start ASC;

This SQL is almost human-readable (and understandable).