CODEWITHSUNDEEP
phpmysqlsql
PacPac
PacPac

Reputation: 261

Join between two table don't return rows with no link between the tables

I've two tables:

___Rooms:

|--------|------------|
| ROO_Id | ROO_Status |
|--------|------------|
| 47     | active     |
| 48     | active     |
| 49     | active     |
|--------|------------|

___Availabilities:

|--------|------------|------------|------------|
| AVA_Id | AVA_RoomId | AVA_Date   | AVA_Status |
|--------|------------|------------|------------|
| 1      | 47         | 2019-02-25 | Open       |
| 2      | 48         | 2019-02-26 | Open       |
| 4      | 47         | 2019-02-28 | Close      |
| 5      | 47         | 2019-02-25 | Open       |
|--------|------------|------------|------------|

I would like to get info for both of these tables for a range of dates.

So my query is the following:

SELECT ROO_Id, AVA_Id, AVA_Date, AVA_Status 
FROM ___Rooms  
LEFT JOIN ___Availabilities 
    ON ___Rooms.ROO_Id = ___Availabilities.AVA_RoomId 
WHERE ROO_Status!="inactive"
    AND AVA_Date BETWEEN "2019-02-24" AND "2019-03-10" 
ORDER BY ROO_Id ASC

The problem is the query don't take all the ___Rooms rows. For example, Room #49 don't have a record in ___Availabilities but I need to have it in the final result.

Do you know why ?

Thanks.

Upvotes: 1

Views: 33

Answers (1)

JeffUK
JeffUK

Reputation: 4241

Using a left join is correct, but by filtering on the AVA_Date column in a where clause you will exclude any rows where AVA_Date is null.

Move that filter to the on clause and your left-join will work as expected

SELECT ROO_Id, AVA_Id, AVA_Date, AVA_Status 
FROM ___Rooms  
LEFT JOIN ___Availabilities 
    ON ___Rooms.ROO_Id = ___Availabilities.AVA_RoomId
    AND AVA_Date BETWEEN "2019-02-24" AND "2019-03-10" 

WHERE ROO_Status!="inactive"
ORDER BY ROO_Id ASC

Upvotes: 1

Related Questions