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

    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:
        • 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))
          • 2. Re: Join Logic Help
            Frank Kulash

            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
              indra budiantho
              /* 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
                    (SELECT *
                       FROM t
                     SELECT *
                       FROM u))
              SELECT *
                FROM a
               WHERE NOT EXISTS (SELECT 1
                                   FROM v
                                  WHERE a.a_id = v.a_id)