Adjit Adjit - 11 days ago 6
SQL Question

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

ITEMNUM
s 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?

Answer

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
Comments