CODEWITHSUNDEEP
sqlsql-server
Adjit
Adjit

Reputation: 10305

Joining 2 tables with sql

I am trying to join these 2 tables based on the left table and matching the row values. The right table does not necessarily have all ITEMNUMs that are in the left table. And not every VNDITNUM is going to have an ITEMNUM that is in the left hand table.

LEFT TABLE (559 rows)
--Filtered based on column IVCOGSIX = 137
SELECT 
    ITEMNMBR
    FROM dbo.IV00101
    WHERE IVCOGSIX = 137
+---------+
| ITEMNUM |
+---------+
| Item 1  |
| Item 2  |
| Item 3  |
| Item 4  |
+---------+

RIGHT TABLE (553 rows)
--Filtered based on column VENDORID = 90505EP
SELECT 
    ITEMNMBR,
    VNDITNUM
    FROM dbo.IV00103
    WHERE VENDORID = '90505EP'
+---------+-------------+
| ITEMNUM |  VNDITNUM   |
+---------+-------------+
| Item 1  | VendorNum 1 |
| Item 2  | VendorNum 2 |
| Item 4  | VendorNum 4 |
| Item X  | VendorNum 5 |
+---------+-------------+

The output that I am trying to get is this -

JOINED TABLE (559 rows)
+---------+-------------+
| ITEMNUM |  VNDITNUM   |
+---------+-------------+
| Item 1  | VendorNum 1 |
| Item 2  | VendorNum 2 |
| Item 3  | NULL        |
| Item 4  | VendorNum 4 |
+---------+-------------+

However, my query keeps giving me only 548 rows, and I'm not necessarily sure what rows it is leaving out.

Here is my current query -

SELECT 
    cogs.ITEMNMBR,
    vin.VNDITNUM
    FROM METRO.dbo.IV00101 cogs
    LEFT JOIN METRO.dbo.IV00103 vin
        on vin.ITEMNMBR = cogs.ITEMNMBR
    WHERE
        cogs.IVCOGSIX = 137
        AND vin.VENDORID = '90505EP'

How can I join these 2 tables to get the 559 rows of the left table and any matching items from the right table?

Upvotes: 1

Views: 113

Answers (3)

DForck42
DForck42

Reputation: 20387

To explain what happened, essentially SQL Server will join the tables together first, and then run the where clause on the results (it's a bit more complicated then that, but this is the general way it works). The WHERE clause as you originally ran it will remove anything that doesn't have vin.VENDORID = '90505EP', essentially stripping out the nulls from the result set. and returning the results of an inner join.

@SeanLange's answer works around this by shifting the filter on VENDORID into the join clause, essentially saying "filter dbo.IV00103 where VENDORID = '90505EP and then left outer join on that result"

I call (and have seen it called this before) as an Accidental Inner Join.

Upvotes: 1

Freak78
Freak78

Reputation: 171

You need a Left Outer Join, so the Left table will output the full content: SELECT cogs.ITEMNMBR, vin.VNDITNUM FROM METRO.dbo.IV00101 cogs LEFT OUTER JOIN METRO.dbo.IV00103 vin on vin.ITEMNMBR = cogs.ITEMNMBR WHERE cogs.IVCOGSIX = 137 AND vin.VENDORID = '90505EP'

Upvotes: -2

Sean Lange
Sean Lange

Reputation: 33581

Your join predicate vin.VENDORID = '90505EP' has effectively turned your left join into an inner join.

Try this instead.

SELECT 
    cogs.ITEMNMBR,
    vin.VNDITNUM
    FROM METRO.dbo.IV00101 cogs
    LEFT JOIN METRO.dbo.IV00103 vin
        on vin.ITEMNMBR = cogs.ITEMNMBR
        AND vin.VENDORID = '90505EP'
    WHERE
        cogs.IVCOGSIX = 137

Upvotes: 7

Related Questions