SQL: select items from one table only if in other table they have both columns with same value

Monika Slocinska 0
Viewed 20

I need some hints.. I got a tables which look like that:

ID ITEM
1 XXXX
2 YYYY
3 ZZZZ
ID ID_2 SUBITEM
1 10 AA
1 11 BB
2 12 CC
2 13 DD
3 14 EE
3 15 FF
3 16 GG
ID_2 value
10 1
11 0
12 1
13 1
14 1
15 1
16 0

I need to get all items where ALL sub-items are = 1.

for example XXXX should not be listed, because BB has value 0.

select distinct         
(table1.item)  
from table1,        
table2,        
table3 
where table1.id = table2.id        
and table2.id_2 = table3.id_2        
and table3.value = 1 
order by table1.item

my code gives me all items wherever 1 is a value

Thanks for help!

plsql
2 Answers

I would use an aggregation approach here:

SELECT i.ID, i.ITEM
FROM items i
INNER JOIN subitems s ON s.ID = i.ID
INNER JOIN vals v ON v.ID_2 = s.ID_2
GROUP BY i.ID, i.ITEM
HAVING COUNT(CASE WHEN v.value != 1 THEN 1 END) = 0;

The COUNT expression above will count 1 every time a value other than 1 appears for a given item. A matching item, then, is one whose non 1 count is zero, meaning all values are equal to 1.

Tim Biegeleisen0

Then zzzz shouldn't be in result set either, as GG has value 0.

Sample data:

SQL> with
  2  a (id, item) as
  3    (select 1, 'xxxx' from dual union all
  4     select 2, 'yyyy' from dual union all
  5     select 3, 'zzzz' from dual
  6    ),
  7  b (id, id_2, subitem) as
  8    (select 1, 10, 'aa' from dual union all
  9     select 1, 11, 'bb' from dual union all
 10     select 2, 12, 'cc' from dual union all
 11     select 2, 13, 'dd' from dual union all
 12     select 3, 14, 'ee' from dual union all
 13     select 3, 15, 'ff' from dual union all
 14     select 3, 16, 'gg' from dual
 15    ),
 16  c (id_2, value) as
 17    (select 10, 1 from dual union all
 18     select 11, 0 from dual union all
 19     select 12, 1 from dual union all
 20     select 13, 1 from dual union all
 21     select 14, 1 from dual union all
 22     select 15, 1 from dual union all
 23     select 16, 0 from dual
 24    )

Query begins here:

 25  select a.item
 26  from a join b on a.id = b.id
 27         join c on c.id_2 = b.id_2
 28  group by a.item
 29  having min(c.value) = max(c.value)
 30     and min(c.value) = 1;

ITEM
----
yyyy

SQL>

In other words: join all three tables. As having clause is to be used, group by the item column and set conditions (you said that all values must be 1).

Littlefoot0
Related