try using a JOIN instead, something like
<CFQUERY NAME="getLocationQuery"
DATASOURCE="#application.lldbDatasource#">
SELECT ll_location.location_id, ll_location.location
FROM ll_lessonstage LEFT JOIN ll_lesson ON ll_lesson.lessonstage_id =
ll_lessonstage.lessonstage_id
LEFT JOIN ll_location ON ll_location.location_id =
ll_lesson.location_id
WHERE ll_lessonstage.lessonstage = '#application.migratedStr#'
ORDER BY seq_no,
upper(location)
that should trim the result set down a little....
Why can't you DISTINCT over both fields? will the same location ever
have different lcationIDs?
-----Original Message-----
From: LANCASTER, STEVEN M. (JSC-OL) (BAR)
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 14, 2002 3:20 PM
To: CF-Talk
Subject: Trimming Duplicates
I have this query and it is returning records with the same name. I want
to
use a function like DISTINCT in my select statement, but I can't because
I
am pulling 2 field any suggestions. Basically when I put #location# it
will
come up with a city and right now it is coming up with it 20 times.
<CFQUERY NAME="getLocationQuery"
DATASOURCE="#application.lldbDatasource#">
SELECT ll_location.location_id, ll_location.location
FROM ll_location,
ll_lesson,
ll_lessonstage
WHERE ll_location.location_id = ll_lesson.location_id
AND ll_lesson.lessonstage_id = ll_lessonstage.lessonstage_id
AND ll_lessonstage.lessonstage = '#application.migratedStr#'
ORDER BY seq_no,
upper(location)
Steven Lancaster
Barrios Technology
NASA/JSC
281-244-2444 (voice)
[EMAIL PROTECTED]
______________________________________________________________________
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
