Hi All,

I'm wondering if there's a way to use a LEFT JOIN table in the WHERE
section and still get it to show the records from the main table (rooms)
that dont have a reservation records associated?

For example....

SELECT *
FROM rooms
LEFT JOIN reservation ON rooms.roomID = reservation.roomID
WHERE reservation.status = 3 OR ???? (no reservation is associated with
the room)

I hope I'm making sense.

Thanks,

Phil.

Yes - that's exactly what the left and right outer joins are meant for.

SELECT rooms.*,reservation.*
FROM rooms
LEFT OUTER JOIN reservation
ON rooms.roomID = reservation.roomID
WHERE reservation.status = 3

if there is no reservation then the columns for reservation table will
contain nulls in the resultset

Raul


On 9/9/2012 8:43 PM, Phil wrote:
>
> I'm wondering if there's a way to use a LEFT JOIN table in the WHERE
> section and still get it to show the records from the main table (rooms)
> that dont have a reservation records associated?
>
> For example....
>
> SELECT *
> FROM rooms
> LEFT JOIN reservation ON rooms.roomID = reservation.roomID
> WHERE reservation.status = 3 OR ???? (no reservation is associated with
> the room)
>
> I hope I'm making sense.
>
> Thanks,
>
> Phil.

On 10/09/2012 11:55 AM, Raul wrote:
> Yes - that's exactly what the left and right outer joins are meant for.
>
> SELECT rooms.*,reservation.*
> FROM rooms
> LEFT OUTER JOIN reservation
> ON rooms.roomID = reservation.roomID
> WHERE reservation.status = 3
>
> if there is no reservation then the columns for reservation table will
> contain nulls in the resultset
>
> Raul

Hi Raul, My point is... "WHERE reservation.status = 3" will STOP the
rooms NOT linked to a reservation showing up...

Thus I wondered if there was something where I can say "WHERE
reservation.status = 3 OR ??????? (i.e still show the room if no match
on the LEFT join)

So that if a reservation with a status of 3 is not matched that room it
STILL shows the room in question.

I hope that makes more sense,

Thanks,

Phil.

Sorry - missed that part.

Try IS NULL :

SELECT rooms.*,reservation.*
FROM rooms
LEFT OUTER JOIN reservation
ON rooms.roomID = reservation.roomID
WHERE reservation.status = 3 OR reservation.status Is NULL

Raul


On 9/9/2012 10:32 PM, Phil wrote:
> , My point is... "WHERE reservation.status = 3" will STOP the rooms NOT
> linked to a reservation showing up...
>
> Thus I wondered if there was something where I can say "WHERE
> reservation.status = 3 OR ??????? (i.e still show the room if no match
> on the LEFT join)
>
> So that if a reservation with a status of 3 is not matched that room it
> STILL shows the room in question.
>
> I hope that makes more sense,

On 10/09/2012 1:40 PM, Raul wrote:
> Sorry - missed that part.
>
> Try IS NULL :
>
> SELECT rooms.*,reservation.*
> FROM rooms
> LEFT OUTER JOIN reservation
> ON rooms.roomID = reservation.roomID
> WHERE reservation.status = 3 OR reservation.status Is NULL
>
> Raul

Thanks Raul, that was one of the first things I tried, but it still
obviously looks for there being a reservation link in the left join and
the status field being NULL.

I'm wondering if there's not something that says if reservation is NOT
MATCHED or linked or something... ?

Thanks for looking,

Phil.

Hi Raul,

I found it... Wow I didn't know the JOIN would allow extra statements in
the join.

I scrapped the WHERE and instead added to the LEFT JOIN...

SELECT *
FROM rooms
LEFT JOIN reservation ON rooms.roomID = reservation.roomID AND
reservation.status = 3

Now it shows all the rooms and the NULL values where expected.

Cheers,

Phil.

Thanks Phil,

That's good tip on the JOIN clause condition

Raul

Hi there

The WHERE clause filters the *result* of the join operation. If the Join Condition excludes some rows, it doesn't matter if the Where condition includes them or not, they aren't there anymore to be included when the WHERE condition is applied.

--
Fernando Dias
[Team Elevate]

Good point Fernando and it clears it up nicely.

The issue Phil ran into was the other side of the coin - the join would
include the rows (where status <> 3) and then the where clause would
filter them hence he would not get the NULL reservation values for those
rooms.

Raul

On 9/10/2012 10:01 AM, Fernando Dias wrote:
> Hi there
>
> The WHERE clause filters the *result* of the join operation. If the Join
> Condition excludes some rows, it doesn't matter if the Where condition
> includes them or not, they aren't there anymore to be included when the
> WHERE condition is applied.
>
> --
> Fernando Dias
> [Team Elevate]

Raul,

Yeah, although I have written a nice and true sentence it's not the reason why the rows were excluded as you pointed out. The real reason, as you just said, is that the reservation.status isn't in fact null, it simply isn't there in the reservations table, the null value only exists in the result dataset.
Tricky thing heh

--
Fernando Dias
[Team Elevate]