Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
LEFT JOIN WHERE question |
Sun, Sep 9 2012 8:43 PM | Permanent Link |
IQA | 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. |
Sun, Sep 9 2012 9:55 PM | Permanent Link |
Raul Team Elevate | 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. |
Sun, Sep 9 2012 10:32 PM | Permanent Link |
IQA | 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. |
Sun, Sep 9 2012 11:40 PM | Permanent Link |
Raul Team Elevate | 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, |
Sun, Sep 9 2012 11:48 PM | Permanent Link |
IQA | 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. |
Mon, Sep 10 2012 12:07 AM | Permanent Link |
IQA | 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. |
Mon, Sep 10 2012 9:09 AM | Permanent Link |
Raul Team Elevate | Thanks Phil,
That's good tip on the JOIN clause condition Raul |
Mon, Sep 10 2012 10:01 AM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Mon, Sep 10 2012 1:26 PM | Permanent Link |
Raul 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] |
Mon, Sep 10 2012 2:04 PM | Permanent Link |
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] |
This web page was last updated on Friday, September 20, 2024 at 05:39 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |