I think you have you're join backwards, when you use a left outer join it
pulls all records from the table to the left in the statement. You need to
do a right join or swap the order of the tables.
Try this:
SELECT R.ResortName, N.Year, N.News
FROM Resorts R LEFT OUTER JOIN News N ON (R.ResortID = N.ResortID AND N.Year
= #CurrentYear#)
this should work-
-----Original Message-----
From: Jim McAtee [mailto:[EMAIL PROTECTED]]
Sent: Monday, June 11, 2001 6:17 PM
To: CF-Talk
Subject: Re: SQL Query Help
> > I could use a bit of help with what I'm guessing is a fairly
> > easy query. I've got the following:
> >
> > Tables:
> > Resorts
> > NewFor
> >
> > Resorts Table:
> > ResortID pk
> > ResortName
> >
> > NewFor Table:
> > NewForID pk
> > ResortID fk
> > Year
> > News
> >
> > In the NewFor table I've got news for 1999, 2000, 2001, etc.
> > for most (but not all resorts). I want to return rows for all
> > resorts, along with any news there may be for the current year.
> >
> > So the output would be:
> > ResortName, News (for only 2001, which may be null)
> >
> > If I do a join, generally I'm left with only resorts which
> > have news records for the current year, which isn't what I'm
> > after.
>
> If I understand correctly, you need to use an outer join rather than an
> inner join. An outer join returns all rows from one table, joined to any
> matching rows from the other one:
>
> SELECT R.ResortName,
> N.News
> FROM Resorts R,
> News N
> WHERE R.ResortID *= N.ResortID
> AND N.Year = #CurrentYear#
>
> You can also use ANSI join syntax:
>
> SELECT R.ResortName,
> N.News
> FROM News N
> LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID
> AND N.Year = #CurrentYear#
>
> In either case, you'll get all the resorts, and within each resort row,
> you'll get any news items for that year if they exist. If they don't, the
> News column will contain NULL for that row.
That was my original attempt, in which case the query returns just resorts
with News records for the current year. Not quite what I expected from an
outer join.
My kludge, until I can find the appropriate SQL approach, is to eliminate
the "N.Year = #CurrentYear#" clause and sort by "ResortID, Year DESC". This
returns many duplicates, with News for prior years attached to those
duplicates. When looping over the results, I use only the first record and
throw away the dupes (same as using CFOUTPUT's GROUP attribute in a loop).
SELECT R.ResortName, N.Year, N.News
FROM News N
LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID
Returns:
Aspen 2001 Stuff...
Aspen 2000 More stuff...
Aspen 1999 Even more stuff...
Breckenridge 1999 Old news...
Keystone NULL NULL
Steamboat 2000 Some news...
Vail 2001 News...
Vail 1999 More....
SELECT R.ResortName, N.Year, N.News
FROM News N
LEFT OUTER JOIN Resorts R ON R.ResortID = N.ResortID
WHERE N.Year = #CurrentYear#
Returns:
Aspen 2001 Stuff...
Vail 2001 News...
I'm trying to find a way of getting to:
Aspen 2001 Stuff...
Breckenridge NULL NULL
Keystone NULL NULL
Steamboat NULL NULL
Vail 2001 News...
Or, just as well, would be to return the _latest_ News record (and NULL for
resorts that have none), and I could suppress the news output if the year
isn't current.
Thanks,
Jim
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
