Re: left outer join on query of query function
actually just looked at my real example and i do have the brackets!!! i think tiredness is getting me now, ill stop till tomorrow and try again, i find sometimes just taking my mind away from it makes it easier when i come back thanks again richard >thanks brad no i hadn't omitted them for simplicity, i will try that as well >then > >thanks again you have been really helpful, will let you know what happens > >>thanks again for your help ~| 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:308418 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: left outer join on query of query function
thanks brad no i hadn't omitted them for simplicity, i will try that as well then thanks again you have been really helpful, will let you know what happens >Ok, good luck. Note: I had to fiddle a little bit for my example to >work. For instance, there needed to be () around the output of the >valuelist etc, but I assumed that had been omitted in your post as an >oversight or for simplicity. > >You can start by dumping your two queries and visually confirming that >what you think exists in them actually does. > >~Brad > >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 ~| 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:308417 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: left outer join on query of query function
Ok, good luck. Note: I had to fiddle a little bit for my example to work. For instance, there needed to be () around the output of the valuelist etc, but I assumed that had been omitted in your post as an oversight or for simplicity. You can start by dumping your two queries and visually confirming that what you think exists in them actually does. ~Brad -Original Message- From: Richard White [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 01, 2008 3:14 PM To: CF-Talk Subject: Re: left outer join on query of query function 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 ~| 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:308414 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: left outer join on query of query function
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. > > > 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' > > > > 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' > > > > 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)#)) > > > > >~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
RE: left outer join on query of query function
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. 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' 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' 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)#)) ~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
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 >This is off the top of head, but what if you did: > >where not (queryA.ID1 in #valuelist(queryB.id1)# >and queryA.ID2 in #valuelist(queryB.id2)# >and queryA.ID3 in #valuelist(queryB.id3)#) > >That way the valuelists are done together as a group, and then the not >applies to all of them combined. > >Does that even make sense? > >~Brad ~| 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:308410 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: left outer join on query of query function
This is off the top of head, but what if you did: where not (queryA.ID1 in #valuelist(queryB.id1)# and queryA.ID2 in #valuelist(queryB.id2)# and queryA.ID3 in #valuelist(queryB.id3)#) That way the valuelists are done together as a group, and then the not applies to all of them combined. Does that even make sense? ~Brad ~| 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:308406 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: left outer join on query of query function
ok im getting there and have narrowed it down to 1 more problem - i hope!! the problem is coming from the second part of the union where i am trying to find the rows that are eliminated in the first inner join part this problem only stems from the fact that i have more than 1 primary key if i had only 1 primary key then saying where queryA.ID not in #valuelist(queryB.id)# would work fine but what i have been doing is the following: where queryA.ID1 not in #valuelist(queryB.id1)# and queryA.ID2 not in #valuelist(queryB.id2)# and queryA.ID3 not in #valuelist(queryB.id3)# and this is returning nothing, which is now obvious to me as it is not looking correctly at all the 3 ids to see which combination of the ids dont exist, it is looking at one column at a time and to see which id1 are not there, then which id2's are not there etc... instead of looking at them as a combination of three does anyone know how i can achieve this: if this explanation is not clear then i can provide an example thanks for your help ~| 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:308401 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: left outer join on query of query function
>Not necessarily. How many records are in joinQuery? It should just have one, >empty record. The point of the first query is to get the columns, but not to >select any records; the empty record is created directly below it. oh yes your right joinquery shouldnt have any records basically i am trying to run this to outer join 2 queries using 3 primary key fields - subjectID, projectID, and testOccasionID so i have adapted it as follows: SELECT * FROM QueryB WHERE QueryB.subjectID = -1 SELECT * FROM QueryA, QueryB WHERE QueryA.subjectID = QueryB.subjectID and QueryA.projectID = QueryB.projectID and QueryA.testOccasionID = QueryB.testOccasionID UNION SELECT QueryA.*, joinQuery.* FROM QueryA, joinQuery WHERE QueryA.subjectID NOT IN (#ValueList(QueryB.subjectID)#) and QueryA.projectID NOT IN (#ValueList(QueryB.projectID)#) and QueryA.testOccasionID NOT IN (#ValueList(QueryB.testOccasionID)#) this doesnt create more rows in fact it actually takes rows off and produces duplicate columns, and doesnt work as a normal sql outer join would instead of using the joinquery.* i will get the column list, take out the primary keys fields so it doesnt duplicate them and then see whats happening thanks dave ~| 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:308355 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: left outer join on query of query function
> 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. Not necessarily. How many records are in joinQuery? It should just have one, empty record. The point of the first query is to get the columns, but not to select any records; the empty record is created directly below it. 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:308332 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: left outer join on query of query function
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: > > >SELECT * >FROM QueryB >WHERE QueryB.ID = -1 > > > > > >SELECT * >FROM QueryA, QueryB >WHERE QueryA.ID = QueryB.ID > >UNION > >SELECT QueryA.*, joinQuery.* >FROM QueryA, joinQuery >WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#) > > >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
RE: left outer join on query of query function
> 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: SELECT * FROM QueryB WHERE QueryB.ID = -1 SELECT * FROM QueryA, QueryB WHERE QueryA.ID = QueryB.ID UNION SELECT QueryA.*, joinQuery.* FROM QueryA, joinQuery WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#) 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:308290 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: left outer join on query of query function
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. the guy indicates that his code is the finished product but now that credibility is lost, so just wondering whether you think it is worth me spending my time really trying to understand what is going on here 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:308289 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: left outer join on query of query function
> i have been following the following link that discusses how > to do a left outer join on a query of query: > > http://www.bealearts.co.uk/blog/2007/06/20/how-to-do-an-outer- > join-in-query-of-queries/ > > this is the code that is meant to do it: > > SELECT * > FROM QueryB WHERE QueryB.ID = -1 > > > > SELECT * FROM > QueryA, QueryB WHERE QueryA.ID = QueryB.ID > > UNION > > SELECT QueryA.*, joinQuery > FROM QueryA, joinQuery > WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#) > > however i am getting a problem on the following line: > > SELECT QueryA.*, joinQuery > > this is producing an error and doesnt make much sense as > surely we must be seleting something from this joinQuery > rather than saying just select it. the error it is producing is: > > Query Of Queries runtime error. > The select column reference [joinQuery] is not a column in > any of the tables of the FROM table list. > > can anyone recognize what this should be? Is there a field in the QueryB table named "joinQuery"? If not, you can't have it in your SELECT clause. Also, you don't specify a join condition in the second query of your UNION statement, which means that it'll return a Cartesian product. 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:308287 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
