freelanceprogrammers.org Forum Index » Cold Fusion

Query of Queries - showing all entries from DB1


View user's profile Post To page top
rgpg_99 Posted: Fri Aug 18, 2006 4:54 pm


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@...
Reply with quote
Send private message
View user's profile Post To page top
c_marnell Posted: Fri Aug 18, 2006 6:11 pm


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
Reply with quote
Send private message
Post new topic Reply to topic
Display posts from previous:   
 

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
Freelace Website Designer - Customer web design and software building.
China Wholesale - Electronics Products
Character Studio - Tutorials and Help