3 Replies Latest reply: May 24, 2012 10:26 PM by indra budiantho

# Join Logic Help

I'm having difficulty with the join logic of a particular query, which is modeled with the following (highly simplified) schema:
``````create table a(
a_id number,
constraint pk_a primary key(a_id));

create table b(
b_id number,
a_id number,
constraint pk_b primary key(b_id),
constraint fk_b_a foreign key(a_id) references a(a_id));

create table c(
c_id number,
b_id number,
constraint pk_c primary key(c_id),
constraint fk_c_b foreign key(b_id) references b(b_id));

insert into a values(1);
insert into a values(2);

insert into b values(1,1);
insert into b values(2,1);
insert into b values(3,2);
insert into b values(4,2);

insert into c values(1,1);
insert into c values(2,2);
insert into c values(3,3);``````
There can be 1-many "b" records associated with each "a" record, but "b" and "c" have a 1-1 relationship. What query will select only the "a" records that have ALL associated "b" records contained in "c"? Given the above data, the query should return only "a=1", since both "b=1" and "b=2" are contained in "c". However, it would NOT return "a=2", since both "b=3" and "b=4" are not contained in "c". The results should be:
``````OUTPUT:
a_id
----
1``````
• ###### 1. Re: Join Logic Help
After thinking about it a bit longer, I came up with the following query, but can anyone suggest a more elegant solution?
``````select a.a_id
from   a
where  a.a_id not in (select b.a_id
from   b
where  b.b_id not in (select c.b_id
from   c))

OUTPUT:
a_id
----
1``````
• ###### 2. Re: Join Logic Help
Hi,

Here's one way:
``````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)
;``````
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.)
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.
• ###### 3. Re: Join Logic Help
``````/* 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)``````