Sybase *= to Ansi Standard with 2 different outer tables for same inner table

Question

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")

Thank you for making it easy for me to try to work out a solution by providing what I needed to create the data to query against. — HLGEM, Aug 30 '12 at 13:40

score 3 · Accepted Answer · answered Aug 29 '12 at 21:59

3

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.

answered Aug 29 '12 at 21:59

HLGEM

94,695
15
113
186

Looks correct to me. I created the tables in a SQL Server database with compatibility level 80 and that's the execution plan it gave. – Martin Smith Aug 29 '12 at 22:12
I did this in an old sql server db that was still set for compatilbility 80 myself. This is why those old implied joins are very bad, you really dont; understand what they were doing. Some of teh code he has to change could have been returning incorrect results for years. That's why the SQL server documentation says that these implied joins could return incorrect results becasue sometimes they interpet as cross joins. I'm glad the OP is fixing this, but to anyone else, if you have used this syntax, it needs to be replaced as it is ambiguous and the server may not be doing what you think. – HLGEM Aug 29 '12 at 22:21
Thanks HLGEM, that solutions works. We will have to address the correctness of the results as I tend to agree they may not be correct. One of the reasons I was struggling for a solution. – user1634341 Aug 30 '12 at 02:38

score 2 · Answer 2 · answered Aug 29 '12 at 21:21

2

An original query is equivalent to:

select * 
from JT1 a
left join JT2 b on a.ID = b.ID
left join JT3 c on c.JOB = b.JOB

answered Aug 29 '12 at 21:21

Robert

25,425
8
67
81

score 1 · Answer 3 · answered Aug 29 '12 at 21:26

1

*= is equivalent to left [outer] join

=* is equivalent to right [outer] join

answered Aug 29 '12 at 21:26

Dave

4,546
2
38
59

score 0 · Answer 4 · edited Mar 29 '17 at 12:02

how to create query...

Sample table : Transact

Entry    Date       PartyIdno  Catage    Credit/Debit  Amount
------   ---------- ---------- --------- ------------  --------
     1   02-01-2016          1   Receipt       C          8,200
     1   02-01-2016          5   Payment       D          8,200
     2   14-02-2016          1     Sales       D         11,200 
     2   14-02-2016          4     Sales       C          6,500
     2   14-02-2016          2     Sales       C          4,700

Output
------

Entry  Date       PartyIdno                     Debit   Credit   Balance
-----  ------     ----------                    ------  -------  -----------                   
                             SubId  SubAmount
                             -----  ---------
  1   02-01-2016      1                                  8,200    8,200 Cr
      02-01-2016               5     8,200 Dr
  2   14-02-2016      1                         11,200            3,000 Dr
      14-02-2016               4     6,500 Cr
      14-02-2016               2     4,700 Cr

SQL Statement :

GLOBALSSS.Open_DB_Connection()

Dim cmd As New Sql Command("Select T1.ENTRY,COALESCE(T1.PARTYIDNO,T2.PARTYIDNO) AS Colour FROM TRANSACT T1 FULL OUTER JOIN (SELECT PARTYIDNO  FROM TRANSACT WHERE ENTRY=1) T2  ON 1 = 0 WHERE T1.PARTYIDNO=1 OR T2.PARTYIDNO-1  ", Connection)

Dim da As New SqlDataAdapter(cmd)

Dim dt As New DataTable

da.Fill(dt)

DataGridView1.DataSource = dt

GLOBALSSS.Close_DB_Connection()

Sybase *= to Ansi Standard with 2 different outer tables for same inner table

4 Answers4

Linked