4 Replies Latest reply on Jan 30, 2013 12:29 PM by Dan Bracuk

    Query of query with left outer join

    jfb00 Level 3

      Hi,

      I cannot use joins in query of query, I try the old method using the ( + ) but no luck "Query Of Queries syntax error. Encountered ( + )."

      Here is an example of my query code:

       

      select p.part_id, s.supplier_name, s.second_name
      from part p, supplier s
      where p.supplier_id = s.supplier_id ( + )
      and  p.second_id = s.second_id ( + );

      PART SUPPLIER_NAME  SECOND_NAME
      ---- ------------------------
      P1   Supplier#1     A
      P2   Supplier#2
      P3
      P4

       

      How can I do the same in query of query syntax?

      Thanks!

        • 1. Re: Query of query with left outer join
          jfb00 Level 3

          I found a solution:

          http://www.bealearts.co.uk/blog/2007/06/20/how-to-do-an-outer-join-in-query-of-queries/

          I am not sure about my second condition. i create the join query for the empty columns.

           

          select part.part_id, supplier.supplier_name, supplier.second_name
          from part, supplier
          where part.supplier_id = supplier.supplier_id
          and  supplier.second_id is null
          union
          select part.part_id, supplier.supplier_name, supplier.second_name
          from part, supplier
          where part.supplier_id = supplier.supplier_id
          and  part.second_id = supplier.second_id
          union
          select part.part_id, joinQuery.supplier_name, joinQuery.second_name
          from part, joinQuery
          where part.supplier_id not in (#ValueList(supplier.supplier_id)#)

           

          Can anyone check and let me know if this is correct?

          My final result have one less row from parts table.

          Thanks

          • 2. Re: Query of query with left outer join
            Dan Bracuk Level 5

            Right general idea but the details are wrong.  I do it like this:

             

            select q1.f1, q2.f2

            from q1, q2

            where q1.f1 = q2.f1

            union

            select q1.f1, 'some constant' f2

            from q1

            where f1 not in (<cfqueryparam value = "#valuelist(q2.f1)# list="yes")

            • 3. Re: Query of query with left outer join
              jfb00 Level 3

              Thanks for you reply and help Dan,

              What about my second condition "SECOND_NAME"?

              The "part" table have supplier_id and second_id. The "supplier" table have few supplier_id's but some second_id's are nulls.

              My first part match the records from "part" table and supplier with the same supplier_id but not second_id.

              The second part of my query match both the supplier_id and second_id.

              The third part will get the "parts" that doen't match with supplier using supplier_id or second_id.

              I hope this is clear.

              Best,

              • 4. Re: Query of query with left outer join
                Dan Bracuk Level 5

                Maybe we should back up a bit.

                 

                How did you get into the situation where you have to do a Q of Q?