Vincent DUPONT Vincent DUPONT - 6 months ago 19
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 [mrbs_users.id], 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 mrbs_users.name=mrbs_entry.create_by
JOIN mrbs_room ON mrbs_entry.room_id = mrbs_room.id
JOIN mrbs_area ON mrbs_room.area_id = mrbs_area.id
WHERE mrbs_entry.start_time BETWEEN "145811700" and "1463985000"
or
mrbs_entry.end_time BETWEEN "1458120600" and "1463992200" and mrbs_users.id = 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

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
       mrbs_users.id = 1
      )

Presumably, you intend:

WHERE (mrbs_entry.start_time BETWEEN 145811700 and 1463985000  or
       mrbs_entry.end_time BETWEEN 1458120600 and 1463992200
      ) and
      mrbs_users.id = 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.