1

I have a list of items Part 1, Part 2, Part 3, Part 4 and Part 5, associated with ID numbers, as follows:

ID  | Item
--  | ----
1   | Part 1
1   | Part 2
1   | Part 3
1   | Part 4
2   | Part 1
2   | Part 2
2   | Part 4
2   | Part 5
3   | Part 2
3   | Part 4
3   | Part 5

I would like a result set of each Part that is NOT found per ID in the Part column, as follows:

ID  | Item
--  | ----
1   | Part 5
2   | Part 3
3   | Part 1
3   | Part 3

Can anyone help with the SQL query to generate my solution recordset?

Regards,

Wayne

Wayne Walker
  • 109
  • 1
  • 9

1 Answers1

0

You can generate all possible combinations if ids and items with a cross join of two select distinct subqueries, and then use the left join antipattern to identify the missing records:

select t1.id, t2.item
from (select distinct id from mytable) t1
cross join (select distinct item from mytable) t2
left join mytable t on t.id = t1.id and t.item = t2.item
where t.id is null
order by t1.id, t2.item

This can also be expressed with not exists:

select t1.id, t2.item
from (select distinct id from mytable) t1
cross join (select distinct item from mytable) t2
where not exists (
    select 1
    from mytable t
    where t.id = t1.id and t.item = t2.item
)
order by t1.id, t2.item

Demo on DB Fiddle:

id | item  
-: | :-----
 1 | Part 5
 2 | Part 3
 3 | Part 1
 3 | Part 3
GMB
  • 216,147
  • 25
  • 84
  • 135
  • GMB, This is brilliant! It is exactly what I need. Thanks so much for providing a solution so quickly!!! – Wayne Walker Nov 22 '19 at 03:50
  • Hi GMB, I finally managed to check out the queries you provided this morning and they work up to a point. They appear to compare the unique values in the source Table and then find the missing values. But what I'm attempting to do is make the comparison with the defined list of "Part 1, Part 2, Part 3, Part 4 and Part 5", irrespective of which values are in the source Table. Do you know how I would check against a set-in-stone list of values? – Wayne Walker Nov 25 '19 at 17:23
  • @WayneWalker: this is a different question... Managing comma-separated values is quite complex, and this kind of setup is discouraged in relational databases (you can see [this famous SO post](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) for the long story). Bottom line, my post answered the question that you have asked, there is no reason to unaccept it. If you have another question, then you should open a new question. – GMB Nov 25 '19 at 18:31
  • GMB, my apologies. I've only asked a couple of question before so I guess I've not been clear enough on my requirement. I didn't mean for the list to be referenced from a comma separated list, but rather from a Table that just has the Set values listed in a column. But I'll do as you say and ask another question, hopefully clearly explaining my needs this time. Again, sorry for messing this up. – Wayne Walker Nov 25 '19 at 22:34
  • @WayneWalker: no problem at all. – GMB Nov 25 '19 at 23:32