oddness with a left join query....
-
The code logic was designed to not have a record in the QuoteStatus table if the record was open, i.e. a new quote. Any other status on the quote was recorded in this status table like void, complete, etc... Why you ask? Well its not my design and I have to work with it. Regardless, I noticed an odd side effect of this design and was hoping someone can clear it up for me. I have one query as this...
SELECT sq.QuoteID, sq.OrderTime, qs.Status
FROM SalesQuote AS sq
LEFT OUTER JOIN QuoteStatus AS qs ON sq.QuoteID = qs.QuoteID
WHERE OrderTime < '2010-02-08'
AND qs.Status!=3The query runs fine, and returns 2 rows of my test data. I should get 3 rows as one of the quotes is open and has no entry in the quotestatus table. I would expect to get a NULL value returned but the row is ommited. When I change to this query...
SELECT sq.QuoteID, sq.OrderTime, qs.Status
FROM SalesQuote AS sq
LEFT OUTER JOIN QuoteStatus AS qs ON sq.QuoteID = qs.QuoteID
WHERE OrderTime < '2010-02-08'will return 5 rows, one of which is the missing quote with a NULL status. Now in code logic I can just test and skip the records of a specific status, but I would prefer to not return the extra data when I do not need to. Any ideas..? There's a free cookie for the right answer! ;)
-
The code logic was designed to not have a record in the QuoteStatus table if the record was open, i.e. a new quote. Any other status on the quote was recorded in this status table like void, complete, etc... Why you ask? Well its not my design and I have to work with it. Regardless, I noticed an odd side effect of this design and was hoping someone can clear it up for me. I have one query as this...
SELECT sq.QuoteID, sq.OrderTime, qs.Status
FROM SalesQuote AS sq
LEFT OUTER JOIN QuoteStatus AS qs ON sq.QuoteID = qs.QuoteID
WHERE OrderTime < '2010-02-08'
AND qs.Status!=3The query runs fine, and returns 2 rows of my test data. I should get 3 rows as one of the quotes is open and has no entry in the quotestatus table. I would expect to get a NULL value returned but the row is ommited. When I change to this query...
SELECT sq.QuoteID, sq.OrderTime, qs.Status
FROM SalesQuote AS sq
LEFT OUTER JOIN QuoteStatus AS qs ON sq.QuoteID = qs.QuoteID
WHERE OrderTime < '2010-02-08'will return 5 rows, one of which is the missing quote with a NULL status. Now in code logic I can just test and skip the records of a specific status, but I would prefer to not return the extra data when I do not need to. Any ideas..? There's a free cookie for the right answer! ;)
I think this is because NULL never matches anything - including
qs.Status != 3Try changing it toAND (qs.Status != 3 OR qs.Status IS NULL) -
I think this is because NULL never matches anything - including
qs.Status != 3Try changing it toAND (qs.Status != 3 OR qs.Status IS NULL)
