Sybase *= to Ansi Standard with 2 different outer tables for same inner table
I am trying to migrate some legacy procedural code. I am having trouble figuring out the ANSI standard syntax to produce the same results.
Below is one of the many combinations I have tried. What is the inner table for the second join, is it the output from the first join or is it the source table.
Please help I have a lot of code to change.
Original SQL Statement
select * from
JT1 a, JT2 b, JT3 c
where a.ID *= b.ID
and c.JOB *= b.JOB
My Conversion
select *
from JT1 a
left outer join JT2 b
on a.ID = b.ID
right outer join JT3 c
on c.JOB = b.JOB
Below is the SQL table definitions and sample data.
Create table JT1 (
ID int(4) not null,
NAME char(20) not null)
Create table JT2 (
ID int(4) not null,
JOB char(20) not null)
Create table JT3 (
JOB char(20) not null,
DUTY char(20) not null)
INSERT INTO dbo.JT1 VALUES(10, "Saunders")
INSERT INTO dbo.JT1 VALUES(20, "Pernal")
INSERT INTO dbo.JT1 VALUES(30, "Marenghi")
INSERT INTO dbo.JT2 VALUES(20, "Sales")
INSERT INTO dbo.JT2 VALUES(30, "Clerk")
INSERT INTO dbo.JT2 VALUES(30, "Mgr")
INSERT INTO dbo.JT2 VALUES(40, "Sales")
INSERT INTO dbo.JT2 VALUES(50, "Mgr")
INSERT INTO dbo.JT3 VALUES("Mgr","Evaluate")
INSERT INTO dbo.JT3 VALUES("Mgr","Reports")
INSERT INTO dbo.JT3 VALUES("Mgr","Meeting")
INSERT INTO dbo.JT3 VALUES("Clerk","Stocking")
INSERT INTO dbo.JT3 VALUES("Clerk","Customer Request")
OK it took me awhile but try this:
select a.ID, a.NAME, b.ID, b.JOB, a.JOB, a.DUTY
from (Select * from #jt1
cross join #jt3 ) a
left outer join #jt2 b
on a.ID = b.ID and a.job = b.job
The problem with using that left join operator mulitple times is that you really had a hidden cross join in there. This should get the right results, As to whether the results have been incorrect all along due to developers not undersatnding waht they were doing, only you can tell.
原始查询相当于：
select *
from JT1 a
left join JT2 b on a.ID = b.ID
left join JT3 c on c.JOB = b.JOB
*= is equivalent to left [outer] join
=* is equivalent to right [outer] join