try OUTER:
<cfquery name="info" datasource="myDSN">
SELECT skiareas.*,
skiareanews.news
FROM skiareas LEFT OUTER JOIN skiareanews ON skiareas.areacode =
skiareanews.areacode
WHERE skiareas.areacode = 'co01'
AND skiareanews.season = 2000
</cfquery>
-----Original Message-----
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 11, 2000 5:59 PM
To: cf-talk
Subject: OT: SQL - One or two queries?
I've got a situation where I'm not sure if it's better to pull data using
a single SQL statement, or use two.
The database relationship is this: One to Many, where I've got a table of
ski areas and a table of 'new for XXXX season' data where I have new
features for a given area in successive seasons, 1998, 1999, 2000, etc.,
one record per ski area/season.
So I want to pull up information about the area, plus the 'news' for the
current ski season. Following the advise of using as few queries as
possible, I tried to construct an outer join to pull a single record with
the needed data. The outer join is necessary, since there may be no news
for a given area in a given season.
<cfquery name="info" datasource="myDSN">
SELECT skiareas.*,
skiareanews.news
FROM skiareas LEFT JOIN skiareanews ON skiareas.areacode =
skiareanews.areacode
WHERE skiareas.areacode = 'co01'
AND skiareanews.season = 2000
</cfquery>
But this returns zero records when there are no news records for the
current season. I suspect the problem may be in the WHERE clause, but I
don't know how to formulate it correctly. By removing the 'AND
skiareanews.season = 2000' part, I get results, but of course it sometimes
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
