Query of query with left outer join

[jfb00]

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!

[jfb00]

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

[Dan_Bracuk]

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

[jfb00]

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,

[Dan_Bracuk]

Maybe we should back up a bit.

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