Need help to make left outer join

[former_member584790]

HI I have this union SQL and need to try a LEFT OUTER JOIN on the CATS table - see if that helps you get other data. I am not getting the right data currently. I am not sure how to make a left outer join.

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP10200.ITEMNMBR,

SOP10200.SOPNUMBE,

SOP10200.QUANTITY,

SOP10200.OXTNDPRC,

SOP10200.SOPTYPE,

SOP10100.DOCDATE,

'Current' as source

FROM ((((PBS.dbo.SOP10200

SOP10200 INNER JOIN PBS.dbo.IV00101 IV00101 ON

SOP10200.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN

PBS.dbo.SOP10100 SOP10100 ON

(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND

(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))

INNER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL)

INNER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP10100.CUSTNMBR=RM00101.CUSTNMBR

WHERE

RM00101.CUSTNAME = 'Weekend Fun'

UNION ALL

SELECT

IV00101.ITMGEDSC,

IV00101.ITEMDESC,

RM00101.CUSTNAME,

IV40600.UserCatLongDescr,

CATS.UserCatLongDescr,

SOP30300.ITEMNMBR,

SOP30300.SOPNUMBE,

SOP30300.QUANTITY,

SOP30300.OXTNDPRC,

SOP30300.SOPTYPE,

SOP30200.DOCDATE,

'History' as source

FROM ((((PBS.dbo.SOP30300

SOP30300 INNER JOIN PBS.dbo.IV00101 IV00101 ON

SOP30300.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN

PBS.dbo.SOP30200 SOP30200 ON

(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND

(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE))

INNER JOIN

PBS.dbo.IV40600 IV40600 ON

IV00101.ITMGEDSC=IV40600.USCATVAL)

INNER JOIN

PBS.dbo.IV40600 CATS ON

IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN

PBS.dbo.RM00101 RM00101 ON

SOP30200.CUSTNMBR=RM00101.CUSTNMBR

WHERE

RM00101.CUSTNAME = 'Weekend Fun'

[Former Member · Accepted solution]

Does changing "INNER JOIN" to "LEFT OUTER JOIN" for the CATS join not work? What happens?

[former_member584790 · Accepted solution]

yes there was no change to the data. it is something else that is the problem having to do with the history tables, where the data is really stored.