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
