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. The "*=" says select
all records from left. Conversely, you could do a "=*" to do a right outer
join.
.......................
Ben Nadel
www.bennadel.com
-----Original Message-----
From: Roberto Perez [mailto:[EMAIL PROTECTED]
Sent: Friday, August 18, 2006 12:57 AM
To: CF-Talk
Subject: 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:250271
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
