inner join with a OR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

inner join with a OR

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-02-28 : 11:27:02

Hi there guys, could anyone please advise if the red text is permissible in T/sql syntax where I want to join on the date or one day minus the date in the same table please ?

UPDATE [zzzExtra Tran Details] INNER JOIN [_EFT Records from Old System] ON ([zzzExtra Tran Details].store_name = [_EFT Records from Old System].[Store Name]) AND ([zzzExtra Tran Details].[Sequence Nr] = [_EFT Records from Old System].[Sequence Number]) AND ([zzzExtra Tran Details].amount = [_EFT Records from Old System].Amount) AND ([zzzExtra Tran Details].trans_date = [_EFT Records from Old System].Date OR [zzzExtra Tran Details].trans_date -1 = [_EFT Records from Old System].Date ) AND ([zzzExtra Tran Details].[Account Nr] = [_EFT Records from Old System].[Account Nr]) SET [_EFT Records from Old System].[Till trans_time] = [zzzExtra Tran Details]![trans_time], [_EFT Records from Old System].[Till trans_no] = [zzzExtra Tran Details]![trans_no], [_EFT Records from Old System].registration = [zzzExtra Tran Details]![registration], [_EFT Records from Old System].product_desc = [zzzExtra Tran Details]![product_desc], [_EFT Records from Old System].price_litre = [zzzExtra Tran Details]![price_litre], [_EFT Records from Old System].litres = [zzzExtra Tran Details]![litres], [_EFT Records from Old System].[Fuel Amount] = [zzzExtra Tran Details]![Fuelvalue]
WHERE ((([_EFT Records from Old System].[Till trans_time]) Is Null));

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-28 : 11:51:39

The red text is permissable - you are comparing the complete value (date and time) of each column so if dates are the same and times are different you won't get a match.

However, your statement is wrong, you can't say
UPDATE table
INNER JOIN table ...

You need to have your column assignment/s before you start joining, so something like

UPDATE table
SET col1 = x
FROM table
INNER JOIN table ...

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-02-28 : 11:56:42

its a bit naughty of me really snsql but this is a access query that I need to compare a date without the time field to a date in another table also without the time field or that date minus one day.

But i think you have answered my question thanks.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-28 : 11:58:51

There is an Access forum...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources