CODEWITHSUNDEEP
sql
Vincent DUPONT
Vincent DUPONT

Reputation: 71

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.

Upvotes: 1

Views: 60

Answers (2)

Vasily
Vasily

Reputation: 5782

The problem you've faced caused by the incorrect condition WHERE.
So, should be:

  WHERE (mrbs_entry.start_time BETWEEN 145811700 AND 1463985000 )
         OR
        (mrbs_entry.end_time BETWEEN 1458120600 AND 1463992200 AND mrbs_users.id = 1)

Moreover, when you use only INNER JOIN (JOIN) then it be better to avoid WHERE clause, because the ON clause is executed before the WHERE clause, so criteria there would perform faster.
Your query in this case should be like this:

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 
          AND
          (mrbs_entry.start_time BETWEEN 145811700 AND 1463985000 
            OR ( mrbs_entry.end_time BETWEEN 1458120600 AND 1463992200 AND mrbs_users.id = 1)       
          )         
     JOIN mrbs_area ON mrbs_room.area_id = mrbs_area.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 2

Related Questions