Vincent DUPONT Vincent DUPONT - 1 year ago 61
SQL Question

The "where" condition worked not as expected ("or" issue)

I have a problem to join thoses 4 tables

Model of my database

I want to count the number of reservations with different sorts (user [], room [mrbs_room.room_id], area [mrbs_area.area_id]).

Howewer when I execute this query (for the user (id=1) )

SELECT count(*)
FROM mrbs_users JOIN mrbs_entry ON
JOIN mrbs_room ON mrbs_entry.room_id =
JOIN mrbs_area ON mrbs_room.area_id =
WHERE mrbs_entry.start_time BETWEEN "145811700" and "1463985000"
mrbs_entry.end_time BETWEEN "1458120600" and "1463992200" and = 1

The result is the total number of reservations of every user, not just the user who has the
id = 1

So if anyone could help me.. Thanks in advance.

Answer Source

Use parentheses in the where clause whenever you have more than one condition. Your where is parsed as:

WHERE (mrbs_entry.start_time BETWEEN "145811700" and "1463985000" ) or
      (mrbs_entry.end_time BETWEEN "1458120600" and "1463992200" and = 1

Presumably, you intend:

WHERE (mrbs_entry.start_time BETWEEN 145811700 and 1463985000  or
       mrbs_entry.end_time BETWEEN 1458120600 and 1463992200
      ) and = 1

Also, I removed the quotes around the string constants. It is bad practice to mix data types, and in some databases, the conversion between types can make the query less efficient.