freelanceprogrammers.org Forum Index » Cold Fusion
Query of Queries - showing all entries from DB1
Joined: 01 Apr 2005
Posts: 16
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
rgpg@...
Joined: 18 Aug 2006
Posts: 1
Query of Queries - showing all entries from DB1
I am
not a SQL expert, but I think you should be able to get the results you want by
creating an Outer Join in your query.
Cody
-----Original Message-----
From: cold_fusion@yahoogroups.com
[mailto:cold_fusion@yahoogroups.com] On
Behalf Of Roberto Perez
Sent: Friday, August 18, 2006 7:55
AM
To: cold_fusion@yahoogroups.com
Subject: [cold_fusion] 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
rgpg@technologist.com
All times are GMT
Page 1 of 1
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Freelace Website Designer - Customer web design and software building.
China Wholesale - Electronics Products
Character Studio - Tutorials and Help
China Wholesale - Electronics Products
Character Studio - Tutorials and Help