Adjit Adjit - 1 month ago 5
SQL Question

Joining tables to get all rows from left and all rows from right including unmatched rows

I am trying to combine 2 tables -

IV00101
and
SOP30300
.
IV00101
contains all of our part numbers that are setup in the database, while
SOP30300
contains all of our line items sold. If there is a non-stock item that is sold (aka not in
IV00101
) it will be in
SOP30300
where I can retrieve it using
CSLSINDX =137
. What I need to join the tables by is part numbers, but I need all rows from
SOP30300
regardless of whether or not the part number is in
IV00101


Here is my attempt at it - but I know it is missing some things because when I exclude the
IV00101
table, I get more rows returned (1575 w/o
IV00101
Join ; 1182 w/ Join)

(SELECT
partTable.SOPNUMBE,
partTable.LNITMSEQ,
partTable.[Ship To Customer],
partTable.UNITCOST,
partTable.QUANTITY,
partTable.CSLSINDX,
cogsTable.ITEMNMBR,
cogsTable.ITMSHNAM
FROM
(SELECT
parts.ITEMNMBR,
parts.SOPNUMBE,
parts.LNITMSEQ,
parts.ShipToName,
parts.CNTCPRSN AS [Ship To Customer],
parts.UNITCOST,
parts.QUANTITY,
parts.CSLSINDX
FROM METRO.dbo.SOP30300 parts WHERE SOPNUMBE like 'I%' AND CSLSINDX = 137
) partTable

LEFT JOIN
(SELECT
cogs.ITEMNMBR,
cogs.ITMSHNAM
FROM METRO.dbo.IV00101 cogs WHERE IVCOGSIX = 137 AND ITMSHNAM <> 'TM' AND ITMSHNAM <> 'Rebate'
)cogsTable
ON cogsTable.ITEMNMBR = partTable.ITEMNMBR
) partNumbers


- Returns 1182 Rows

(SELECT
partTable.SOPNUMBE,
partTable.LNITMSEQ,
partTable.[Ship To Customer],
partTable.UNITCOST,
partTable.QUANTITY,
partTable.CSLSINDX,
partTable.ITEMNMBR

FROM
(SELECT
parts.ITEMNMBR,
parts.SOPNUMBE,
parts.LNITMSEQ,
parts.ShipToName,
parts.CNTCPRSN AS [Ship To Customer],
parts.UNITCOST,
parts.QUANTITY,
parts.CSLSINDX
FROM METRO.dbo.SOP30300 parts WHERE SOPNUMBE like 'I%' AND CSLSINDX = 137
) partTable
) partNumbers


-Returns 1575 rows

Would someone please be able to tell me how I am incorrectly joining the two tables?

Answer

Left join:

SELECT
  parts.SOPNUMBE,
  parts.LNITMSEQ,
  parts.CNTCPRSN AS [Ship To Customer],
  parts.UNITCOST,
  parts.QUANTITY,
  parts.CSLSINDX
  cogs.ITEMNMBR,
  cogs.ITMSHNAM
FROM METRO.dbo.SOP30300 parts 
LEFT JOIN METRO.dbo.IV00101 cogs ON cogs.ITEMNMBR = parts.ITEMNMBR AND cogs.IVCOGSIX = 137 AND cogs.ITMSHNAM <> 'TM' AND cogs.ITMSHNAM <> 'Rebate'
WHERE parts.SOPNUMBE like 'I%' AND parts.CSLSINDX = 137

Note, a number of the requirements on IV00101 have to be in the ON part of the join so they are a filter on the JOIN and not on whole query. If you put those in the WHERE you make an inner join which would return less results.

The reason it would become an inner join is because you can't filter on NULL so when you would normally get a null for cogs columns you just exclude them -- this is the equivalent of an inner join.

Comments