Skip to main content
J
jfb00
Inspiring
January 30, 2013
Question

Query of query with left outer join

  • January 30, 2013
  • 1 reply
  • 1034 views

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!

    This topic has been closed for replies.

    1 reply

    J
    jfb00Author
    Inspiring
    January 30, 2013

    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

    D
    Dan_Bracuk
    Inspiring
    January 30, 2013

    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")

    J
    jfb00Author
    Inspiring
    January 30, 2013

    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,

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices