This content has been marked as final. Show 3 replies
Here's one way:
Most things that you can do with IN sub-queries can also be done with joins, or with EXISTS sub-queries. (Most things that can be done with EXISTS sub-queries can also be done wioth joins or IN sub-queries. However, there are lots of things that can be done only with joins.)
SELECT a.a_id FROM a JOIN b ON a.a_id = b.a_id LEFT OUTER JOIN c ON b.b_id = c.b_id GROUP BY a.a_id HAVING COUNT (b.b_id) = COUNT (c.b_id) ;
If you don't like one, try the others.
Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful.
Don't forget to post the results you want from that data, no matter how simple those results are, and your version of Oracle.
/* Formatted on 2012/05/25 10:25 (Formatter Plus v4.8.8) */ WITH t AS (SELECT b.* FROM b, a WHERE a.a_id = b.a_id), u AS (SELECT b.* FROM b, c WHERE b.b_id = c.b_id), v AS (SELECT * FROM t MINUS (SELECT * FROM t INTERSECT SELECT * FROM u)) SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM v WHERE a.a_id = v.a_id)