PRE ANSI-92 Left Join Syntax (Sybase)

Question

What is the syntax to specify a Left Join using pre ANSI-92 syntax (i.e. *=) when part of the Where clause has an 'equal to some constant' condition ? (in this case 100 is the constant)

Example:

SELECT t1.ID, t.* 
FROM (select * from SybaseTable where ID=1) t, SqlServerTable t1
WHERE t1.ID *= 100 and t1.SeqNo *= t.SeqNo

In this case I want all records from derived table 't', even if 't1' has no match. I want t1.ID to return NULL in the Select clause when a matching row on SeqNo does not exist in 't'.

The error I am receiving is "Both terms of an outer join must contain columns".

I am executing this query in SQL Server 2005 for now, but it will eventually be used against a Sybase implementation, which requires the old join syntax.

thanks in advance

Answer 1

I think this will do it, but you'll have to test to be certain:

SELECT t1.ID, t.* 
FROM (select * from SybaseTable where ID=1) t, SqlServerTable t1
WHERE t.SeqNo *= t1.SeqNo AND 100 *= t1.ID

Notice that I reversed the order on the SeqNo part, to better indicated which is the left side of the join.