2

ok so my problem is that I have a set of results:

ID  CODE
1   A
1   B
3   C

I also have another table of:

CODE
A
B
C

What I want to get using SQL is a query of CODES that each result from table 1 is missing. So basically:

ID  CODE
1   C
3   A
3   B

Any help would be great.

user1577584
  • 21
  • 1
  • I Don't understand your question. Doesn't your first table already have a 'code' for each 'id'? Maybe what you are looking for is PHP - Array_diff - http://php.net/manual/en/function.array-diff.php – d-_-b Aug 05 '12 at 16:30

2 Answers2

1

You can use:

SELECT     a.id, b.code
FROM       (SELECT DISTINCT id FROM idcodes) a
CROSS JOIN codes b
LEFT JOIN  idcodes c ON a.id = c.id AND b.code = c.code
WHERE      c.id IS NULL
ORDER BY   a.id, b.code

If you have another table that stores unique entries for id, it would be better to just use that table instead rather than a DISTINCT subselect:

SELECT     a.id, b.code
FROM       ids a
CROSS JOIN codes b
LEFT JOIN  idcodes c ON a.id = c.id AND b.code = c.code
WHERE      c.id IS NULL
ORDER BY   a.id, b.code

SQLFiddle Demo

Zane Bien
  • 22,685
  • 6
  • 45
  • 57
0

You can use exists. Construct the full list of possible variations with a Cartesian join and then ensure what you have isn't in this list.

select id, code
  from idcode_table x
 where not exists ( select 1 
                      from idcode_table a
                     cross join code_table b
                     where b.code = x.code
                       and a.id = x.id )

This can also be re-written with a not in.

select id, code
  from idcode_table
 where (id, code) not in ( select distinct a.id, b.code
                             from idcode_table a
                            cross join code_table b )

The distinct is optional. It will make it slower to construct the list of possibles but quicker to determine if you have one of them already. I'd test it to see which is quicker.

Ben
  • 51,770
  • 36
  • 127
  • 149