I am using SQL through Toad

So I have data where I am trying to see if an internal item number exists in table 1 and does not exist in table 2 for the same mfg number. This would be really easy if I had clean data but I don't and I can't change it so I have duplicate items in table 2 where some have an item number and some don't.

Here is what I need:

Find a mfg number that has an internal item number in table 1 and check to see if for the same mfg number it has an internal item number in any of the duplicate records on table 2. Do not return anything if any record has an item number in table 2.

Example data:

Table 1:

Mfg Number | Item number

1. 1 | 123
2. 2 | 456
3. 3 | 789

Table 2:

Mfg Number | Item number

1. 1 | 123
2. 1 | 123
3. 1 | NULL
4. 2 | NULL
5. 2 | NULL
6. 2 | NULL
7. 3 | 789
8. 3 | 789
9. 3 | 789

This is what I tried:

SELECT DISTINCT * FROM Table 1 INNER JOIN Table 2 ON Mfg Number = Mfg Number WHERE table 1 item number IS NOT NULL AND table 2 item number IS NULL

This produced false data because it returns both item 1 and item 2. Even though item 1 does have an item number in some of the records, I am only seeing the one where it is null.

What I would want to see is only item 2 because ALL of the item number fields for that item are null.

Not sure if this is possible but if it is I figured this would be the place to find it.

Thanks!

CodePudding user response：

See if this gets you what you're after.

SELECT DISTINCT a.*
FROM #tblTable1 a 
WHERE NOT EXISTS (SELECT 'X'
                FROM #tblTable2 b
                WHERE a.MfgNumber = b.MfgNumber
                    AND a.Itemnumber = b.Itemnumber)

CodePudding user response：

So you want all columns from Table1 where all rows in Table2 with that Mfg_Number have NULL Item_Number?

SELECT Table1.* FROM Table1 
INNER JOIN (
  SELECT Mfg_Number FROM Table2
  GROUP BY Mfg_Number 
  HAVING MAX(Item_Number) IS NULL
  ) AS AllNull
 ON Table1.Mfg_Number = AllNull.Mfg_Number
WHERE Table1.Mfg_Number IS NOT NULL;