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

Question

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?

See http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join?rq=1 — Zack, Oct 11 '16 at 20:58
I would assume you get more rows because there are rows in `SOP30300` that have _multiple_ matches in `IV00101`. aNull value in `cogsTable.ITEMNMBR` would tell you if there was a match or not. — D Stanley, Oct 11 '16 at 20:59
Seems that you are looking for a CROSS JOIN between both Tables. — Marc Guillot, Oct 11 '16 at 20:59
You seem to be massively overcomplicating this query. I do not see any point of using subqueries, this can be achieved by simple join between the 2 tables. — Shadow, Oct 11 '16 at 21:00
What are you joining on? Item number? @Shadow is right. There's no need for all of the subqueries. You should just have to select from the SOP30300 table and LEFT JOIN the parts table on some key. — Brandon, Oct 11 '16 at 21:01
@Shadow I definitely think I am over complicating this, I guess I've been staring at it for too long. Originally I was joining the tables the other way and excluding entries that weren't in `SOP30300`. I guess I should try this again... — Adjit, Oct 11 '16 at 21:06
This can't be the code that you are running you have an alias and then you don't use the alias in the where clause. Please post the ACTUAL code that got these results. (*I bet the problem will be quite clear then*) — Hogan, Oct 11 '16 at 21:11
@Hogan It may help, but it's about 150 lines. If you still want I can post it. Shadow's suggestion helped me out but the number of returned rows changes slightly depending on how I structure the query and never actually get as many rows as I did originally, so I will have to do some more testing to see why it leaves out certain things — Adjit, Oct 11 '16 at 21:17
@Adjit - don't bother... read my answer and you will understand (I hope) — Hogan, Oct 11 '16 at 21:18

Hogan · Accepted Answer · 2016-10-11T21:21:48.337

0

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.

edited Oct 11 '16 at 21:21

answered Oct 11 '16 at 21:16

Hogan

69,564
10
76
117

did you intend to include the `ON` keyword? – Beth Oct 11 '16 at 21:21
I was trying to keep it from scrolling horizontally, and noticed you missed the `ON` – Beth Oct 11 '16 at 21:22
Thanks Hogan. It definitely clears things up. Appreciate the help and will test this a little more to understand better – Adjit Oct 11 '16 at 21:23
@Adjit -- the way this works is one of the hardest parts of working with joins -- I've made this mistake myself often -- remember to put your join target filters after the `ON` – Hogan Oct 11 '16 at 21:24
@Hogan so now why not put all of the `AND`s into the `WHERE`? or does the `WHERE` have to be the `FROM` table? – Adjit Oct 11 '16 at 22:17
@Hogan, I wanted to read the conditions without scrolling horizontally and just happened to notice the typo, I thought it told you when there were edits pending, but maybe that's only for questions, not answers. Didn't intend for you to lose your changes, but you always have the option to reject edits, so no content was lost. – Beth Oct 11 '16 at 22:40
@Adjit -- you can do it in the WHERE or in the JOIN. Where you put the filter will change the results. – Hogan Oct 13 '16 at 02:06
@hogan in what way would it change the results? – Adjit Oct 13 '16 at 02:54
@Hogan thx, didn't know, thought I'd seen that before. Like I said, just wanted to add LFs so didn't scroll horiz. – Beth Oct 13 '16 at 13:09
@adjit it would change it from a left join to an inner join. Having it in the where would give fewer records. – Hogan Oct 13 '16 at 14:37
@Beth -- As I said it is no problem -- people like you reviewing my answers is what makes SO great. – Hogan Oct 13 '16 at 14:38

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

1 Answers1