MrDeference

SQL Question.

Having a bit of a mare with GetDate().

Can anyone explain why :
SELECT OrderHeader.orderUID, OrderHeader.orderDate FROM OrderHeader INNER JOIN OrderSubItem ON OrderHeader.orderUID = OrderSubItem.orderUID WHERE 60 > DATEDIFF(mi, OrderHeader.orderDate, GETDATE())

would return nothing, whereas the same without the JOIN does. i.e.

SELECT OrderHeader.orderUID, OrderHeader.orderDate FROM OrderHeader WHERE 60 > DATEDIFF(mi, OrderHeader.orderDate, GETDATE())


The join clause is valid, such that the first statement with out the WHERE clause will return data.

The object names and column names have been conjured up to convey meaning, so critical analysis of the table structure or pointing out the uselessness of the join will be ignored , cheers

Another thing. I have an app that is tipping entries into the database about once every 2 seconds. This has been running for 4 days. The timestamp on the entry (generated with GetDate() in the SQL string, rather than through the default entry) is now 9 hours in advance of system time.

Any one got any ideas what is going on?
MS SQL Server 2000, SP3 on XP.
Cheers.

MrDeference

So, thanks to those 35 or so people that had a look.

The answer is:
SELECT OrderHeader.orderUID, OrderHeader.orderDate FROM OrderHeader INNER JOIN OrderSubItem ON OrderHeader.orderUID = OrderSubItem.orderUID WHERE 60 > DATEDIFF(mi, OrderHeader.orderDate, GETDATE())

should read:

SELECT OrderHeader.orderUID, OrderHeader.orderDate FROM OrderHeader INNER JOIN OrderSubItem ON OrderHeader.orderUID = OrderSubItem.orderUID AND 60 > DATEDIFF(mi, OrderHeader.orderDate, GETDATE())

Anyone who can tell me *why* changing from WHERE to AND makes the blindest bit of difference wins my eternal respect.
Cheers.

Fosters

the where is outside the join, whereas the and is tightening the join criteria.

MrDeference

Fosters,
Cheers very much for the reply. I am not sure I understand it though...
If the JOIN criterion is already met, tightening it up after the JOIN should make no difference to the resultant dataset.
If you follow?
What is wrong with my thinking?