asked on

trying to find the differences between 2 tables based on 2 fields..

trying to find the differences between 2 tables based on 2 fields..
project_number
and
Sku

and return records from the table where records are missing from the other table

but i keep getting back repeated records.

select * from [Metrics].[dbo].[Header_Current_missing]
inner join
[Metrics].[dbo].Header_Detail_Current
on
[Metrics].[dbo].Header_Current_missing.PROJECT_NUMBER = [Metrics].[dbo].[Header_Detail_Current].PROJECT_NUMBER
and
[Metrics].[dbo].Header_Current_missing.sku <> [Metrics].[dbo].[Header_Detail_Current].SKU

Like this: Header_Current_missing
project_number sku
111112 2a228
222221 2e342
444445 2x454

Like this: Header_Detail_Current
project_number sku
111112 2a223
222221 2e342
444445 2x454

So the query should return this one record.
Like this: Header_Current_missing
project_number sku
111112 2a228

Thanks
fordraiders

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

I prefer the 2nd example above.

The reasons you are having problems are:
1. the use of INNER JOIN rather than LEFT JOIN. Inner join will only return records from the two tables which match based on the Join criteria. An outer join (LEFT JOIN) will return all of the records from the "first" table and only those records which match from the second table.
2. The use of the "<>" in your join criteria. This will return all record from the two tables which have the same project number, but different skus.

So with this <> in the join, and data that looks like:

SELECT T1.*, T2.* FROM Table1 as T1 INNER JOIN Table2 as T2
ON T1.ProjNum = T2.ProjNum AND T1.ProjNum <> T2.ProjNum

Table #1                                         Table #2
RecNum   ProjNum    SKU                 RecNum    ProjNum      SKU
01        11111      a                     21     11111         a
02        11111      b                     22     11111         b
03        11111      c                     23     11111         c

T1.RecNum   T1.ProjNum    T1.SKU    T2.RecNum   T2.ProjNum   T2.SKU
01             11111         a         22        11111           b
01             11111         a         23        11111           c
02             11111         b         21        11111           a
02             11111         b         23        11111           c
03             11111         c         21        11111           a
03             11111         c         22        11111           b

Open in new window

Fordraiders

ASKER

thanks all !!