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

Solution 1:

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    
      crossjoin #jt3  ) a 
leftouterjoin #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.

Solution 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

Solution 3:

*= is equivalent to left [outer] join

=* is equivalent to right [outer] join

Solution 4:

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