i see, thanks for your help dave, i am actually trying to do this on more than 1 primary key and it is not producing any errors when i add the joinquery.* but it is producing 2 times more rows than i expect, so it must be me missing a where clause somewhere.
seeing as you also indicate this should be working i am going to go through it thoroughly to try to understand it, as outer joins are really needed in query of queries!!! thanks Dave >> i basically have just copied and pasted the code from the >> link i pasted above: do you have any idea how to rectify this >> or even if this would work. > >I hadn't read the link, just looked at your code. Not having read the code, >I didn't realize that joinQuery would contain a single record with empty >strings as values. So, presumably, that would take care of the Cartesian >product problem. If I had to guess, it should be something like this: > ><cfquery name="joinQuery" dbtype="query" > >SELECT * >FROM QueryB >WHERE QueryB.ID = -1 ></cfquery> > ><cfset QueryAddRow(joinQuery) /> > ><cfquery name="result" dbtype="query" > >SELECT * >FROM QueryA, QueryB >WHERE QueryA.ID = QueryB.ID > >UNION > >SELECT QueryA.*, joinQuery.* >FROM QueryA, joinQuery >WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#) ></cfquery> > >The only difference above is that I'm fetching all the columns of joinQuery. > >Dave Watts, CTO, Fig Leaf Software >http://www.figleaf.com/ > >Fig Leaf Software provides the highest caliber vendor-authorized >instruction at our training centers in Washington DC, Atlanta, >Chicago, Baltimore, Northern Virginia, or on-site at your location. >Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:308321 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
