thanks for the description brad, yes sorry i wrote it wrong they do exists in query a and not in query b so i want to find those that exist in querya and not in query b.
however your example is quite detailed and shows that the 'where not ()' clause should do the trick so there must be something else i am missing either in my code or previous outputs. i will output everything slowly to see why it is not returning the rows as expected thanks again for your help >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 > >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:308413 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
