Maybe I should back up and make sure I understand what it is you want.
What you have written is a left outer join, but what you described was a
right outer join.
If you want the records in query B which do not exist in query A, then
you need to flip flop, Query B needs to be in your FROM clause and
Query A needs to be in the value list stuff.
Here is a simple example I just tried that seems to work fine for me.
<cfquery datasource="your_datasource" name="queryA">
SELECT 11 AS ID1, 21 AS ID2, 31 AS ID3, 'exists in both' as
description
UNION SELECT 12, 22, 32, 'exists in both'
UNION SELECT 13, 23, 33, 'exists only in A'
UNION SELECT 14, 24, 34, 'exists only in A'
</cfquery>
<cfquery datasource="your_datasource" name="queryB">
SELECT 11 AS ID1, 21 AS ID2, 31 AS ID3, 'exists in both' as
description
UNION SELECT 12, 22, 32, 'exists in both'
UNION SELECT 15, 25, 35, 'exists only in B'
UNION SELECT 16, 26, 36, 'exists only in B'
</cfquery>
<cfquery dbtype="query" name="qry_left_outer">
SELECT *
FROM queryB
WHERE NOT (queryB.ID1 in (#valuelist(queryA.id1)#) and
queryB.ID2 in (#valuelist(queryA.id2)#) and queryB.ID3 in
(#valuelist(queryA.id3)#))
</cfquery>
<cfdump var="#qry_left_outer#">
~Brad
-----Original Message-----
From: Richard White [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 01, 2008 2:13 PM
To: CF-Talk
Subject: Re: left outer join on query of query function
Hi Brad, i thought that would work but not producing the result as
expected, it still returns no rows on 2 queries where i know queryb has
15 rows that are not in querya therefore it should be returning those 15
but is not
thanks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308412
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
