Vincent DUPONT Vincent DUPONT - 2 years ago 76
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 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
       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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download