RE: Query of Queries - showing all entries from DB1
At 09:29 AM 8/18/2006, Kevin Roche wrote: I think its just a matter of: 1/ Do an Inner Join with QonQ 2/ Do a QonQ query on the left hand table that creates an exact similar result set with null entries for any right table fields and omits any rows from the first query. 3/ Do a QonQ which does a UNION of the the first two. You may have problems with this if the results of 1 are large and can't be converted quickly into a list. Well, I was hoping there would be an easier way. I guess I can import the table in DB2 into DB1 and just do a usual left join with the two tables now inside the same DB. I was hoping the QoQ syntax would allow for a left outer join, but it looks like it doesn't. :-( Thanks, Roberto ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250375 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries - showing all entries from DB1
At 07:30 AM 8/18/2006, you wrote: I think with a query of queries, you can do a left outer join using *= syntax: SELECT * FROM table1, table2 WHERE table1.id *= table2.fkey I AM NOT SURE OF THIS. But I think I read it somewhere. Thanks for the input. I tried it, but CFMX did not like the syntax (I'm using Access, if that makes a difference). I found a link to a fake left outer join script at http://instantbadger.blogspot.com/2006/07/faking-left-outer-join-in-query-of.html A left outer join sound just like what I need to do. Is there an easier way to do it (easier than what is described in the link above)? Thanks in advance, Roberto Perez [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250273 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Query of Queries - showing all entries from DB1
I was going to suggest a union, so that link you found is the way to go IMO. On 8/18/06, Roberto Perez [EMAIL PROTECTED] wrote: I found a link to a fake left outer join script at http://instantbadger.blogspot.com/2006/07/faking-left-outer-join-in-query-of.html A left outer join sound just like what I need to do. Is there an easier way to do it (easier than what is described in the link above)? -- CFAJAX docs and other useful articles: http://www.bifrost.com.au/blog/ ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250279 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Query of Queries - showing all entries from DB1
Roberto, I would suggest a similar series of steps but the link gives a rather complex example since they are trying to write a generic version that works out foir itself waht the queries are. If you do it yourself it may be easier to understand, and it may be possible to make it perform better. I think its just a matter of: 1/ Do an Inner Join with QonQ 2/ Do a QonQ query on the left hand table that creates an exact similar result set with null entries for any right table fields and omits any rows from the first query. 3/ Do a QonQ which does a UNION of the the first two. You may have problems with this if the results of 1 are large and can't be converted quickly into a list. Kevin -Original Message- From: Roberto Perez [mailto:[EMAIL PROTECTED] Sent: 18 August 2006 12:58 To: CF-Talk Subject: RE: Query of Queries - showing all entries from DB1 At 07:30 AM 8/18/2006, you wrote: I think with a query of queries, you can do a left outer join using *= syntax: SELECT * FROM table1, table2 WHERE table1.id *= table2.fkey I AM NOT SURE OF THIS. But I think I read it somewhere. Thanks for the input. I tried it, but CFMX did not like the syntax (I'm using Access, if that makes a difference). I found a link to a fake left outer join script at http://instantbadger.blogspot.com/2006/07/faking-left-outer-join-in-query-of ..html A left outer join sound just like what I need to do. Is there an easier way to do it (easier than what is described in the link above)? Thanks in advance, Roberto Perez [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250287 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Query of Queries - showing all entries from DB1
Hi all, I have a query of queries that brings Region information from DB1, and CityName and Customer information from DB2. The query looks like this: cfquery dbtype=query name=allRecords SELECT listRegions.regionID AS AregionID,listRegions.region AS Aregion, listCities.regionID AS BregionID,listCities.cityName AS BcityName, listCities.customer AS Bcustomer FROM listRegions, listCities WHERE listRegions.regionID = listCities.regionID ORDER BY listRegions.region ASC, listCities.cityName ASC /cfquery Then I output the results with the following code: cfouput query=allRecords group=Aregion #Aregion# cfoutput group=BcityName #BcityName# cfoutput #Bcustomer# /cfoutput /cfouput /cfoutput My problem: if a city has no customers, it does not get listed. But I want that city anyway, with an empty customer entry (a text like no customer found would be swell). Does a QoQ have a syntax similar to left join, right join, etc., to show all entries from one side of the relationship and only matching entries from the other? In this case, I'd like all city names, and only the matching customers for each city (with the cities that have no customers still displayed). Thanks in advance for any pointers and/or ideas. Regards, Roberto Perez [EMAIL PROTECTED] ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250262 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
