SQL query for ids, who never show a special combination

Question

I have 2 tables like

id_a | id_b
-----------
01   | 011
01   | 012
02   | 021
02   | 022

and

id_b |  cl   | ds
------------------
011  | F9.00 | G
012  | F3.00 | G
021  | F9.00 | P
022  | G7.50 | G

the tables are joined on id_b. Now I want to know every Id_a where there is no combination of F9.00 | G. So in this case, my desired result is 02. Even though, the row 01 | 012 | F3.00 | G does not match the F9.00 | G requirement, I dont want to have 01 as an result, because there is the combination 01 |011 | F9.00 | G with 01 as id_a.

Cant think of an easy query at the moment. Maybe some of you have an idea.

Regards, Søren

what RDBMS? mySQL, SQL SERVER else? and what have you tried? this seems like a straight forward exists, not in, or left join where null.. — xQbert, Aug 23 '16 at 12:53
Specify the expected result (as a table, like the ones you already have.) — jarlh, Aug 23 '16 at 12:54
its in kylin, which uses calcite as queryengine. the result should be just one column with the id_a's on the UI — Søren, Aug 23 '16 at 12:56

score 1 · Answer 1 · 2016-08-23T13:21:40.873

Something like this:

select t1.id_a
from t1
  join t2 on t1.id_b = t2.id_b
where (t2.cl, t2.ds) <> ('F9.00', 'G')
group by t1.id_a
having count(*) = (select count(*) 
                   from t1 as t3
                   where t3.id_a = t1.id_a);

This gets the rows where no combination of 'F9.00', 'G' exists and only those where the resulting row count for that is the same as the total rowcount for that id_a.

Edit, after the platform was specified:

The above is ANSI SQL - I don't know if kylin supports ANSI SQL. If where (t2.cl, t2.ds) <> ('F9.00', 'G') doesn't work, you need to use where t2.cl <> 'F9.00' and t2.ds <> 'G'

score 0 · Answer 2 · answered Aug 23 '16 at 12:58

select id_a
from table1
except
select t1.id_a
from table1 t1
inner join table2 t2
on t1.id_b=t2.id_b
where t2.c1 = F9.00
and t2.ds = G

This code will grab the IDs you don't want at the bottom, all the IDs at the top, and then it will exclude them

score 0 · Answer 3 · answered Aug 23 '16 at 13:59

0

You can use CONCAT on columns c1 and ds:

SELECT DISTINCT id_a
FROM T1 INNER JOIN T2
ON T1.id_b = T2.id_b
WHERE id_a NOT IN
(SELECT id_a
 FROM T1 INNER JOIN T2
 ON T1.id_b = T2.id_b
 WHERE CONCAT(c1, ds) = 'F9.00G')

answered Aug 23 '16 at 13:59

kjmerf

4,275
3
21
29

SQL query for ids, who never show a special combination

3 Answers3