Posted by Erland Sommarskog on 06/08/05 01:02

brendan_gallagher_2001@yahoo.co.uk (brendan_gallagher_2001@yahoo.co.uk)
writes:
> Thanks for your reply. Here is the SQL you requested :
>
> Note vw_orderstat is the table representing the view (viewA) -
> Tbl_OrderStatusLog3 is the table (tableB).
>
> Correction to the initial query:Not matter what kind of join I use I
> cannot get BOTH the rows from the view where dateA > getdate()-1 AND
> where dates 1 to 3 are greater than in tableB. Dates 1 to 4 are
> seperate date fields. Could someone please tell me what I am doing
> wrong.

Thanks for your scripts! It really help to sort things out. Your
query looked sound to me, but I had an experience when I ran it...

Normally the confusion with LEFT JOIN is that people have conditions
in the WHERE clause should be in ON, but this an opposite case. With
the condition in ON, you will always get all rows in the view, as long
as they match the date criteria. So you need to add the date condition
to the WHERE clause, and add a condition which says "there is no
orderstat at all):

SELECT v.*
FROM vw_orderstat v
LEFT JOIN Tbl_OrderStatusLog3 t ON t.cficashid = v.cficashid
WHERE v.cashdate = '2005-06-05' -- convert(char(10), getdate(), 120)
AND (v.cficash_entry > t.cficash_entry OR
v.h_entry > t.h_entry OR
v.f_entry > t.f_entry OR
t.cficashid IS NULL)

Also, I hope that the underlying column do casedate is not a char(10)
in real life!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]