So, you're saying that it's returning more than one row of data for some
unique item_nums from papers? The issue is probably that one item_num has
more than one color (which is, after all, why you're joining them).
I think what you probably want to do is output something like this:
Paper #1
blue, pink, red
Paper #2
white, yellow, green
If so, you don't need a distinct, you just need to add some ordering to your
query and group the records on output:
<cfquery name="srch" datasource="myDATA">
SELECT
color.colorCAT,
color.colorNAME,
color.colorID,
colorLOOKUP.itemNUM,
colorLOOKUP.colorID,
papers.*
FROM ( color
INNER JOIN colorLOOKUP ON color.colorID = colorLOOKUP.colorID)
INNER JOIN papers ON colorLOOKUP.itemNUM = papers.ITEM_NUM
WHERE color.colorCAT = '#url.color#'
OR color.colorNAME = '#url.color#'
ORDER BY papers.PA_CATEGORY ASC, color.colorcat, papers.papername
</cfquery>
<cfoutput query="myquery" group="pa_category">
<h1>#pa_category#</h1>
<cfoutput group="papername">
<p>#papername#<br>
<cfoutput>#color#</cfoutput>
</p>
</cfoutput>
</cfoutput>
Note that you should also be using cfqueryparams for your dynamic bits in
the query.
----- Original Message -----
From: "Les Mizzell" <[EMAIL PROTECTED]>
To: "CF-Talk" <[email protected]>
Sent: Thursday, May 12, 2005 11:21 AM
Subject: Select Distinct Question
> OK, here's the current working query. But, it's returning a few
> duplicate records from the "papers" table (last table in the SELECT) so,
> I need to tell it only DISTINCT records from "papers".
>
> If I just put DISTINCT right after "select", I'm obviously still getting
> duplicates.
>
> Ideas?
>
> <cfquery name="srch" datasource="myDATA">
> SELECT
> color.colorCAT,
> color.colorNAME,
> color.colorID,
> colorLOOKUP.itemNUM,
> colorLOOKUP.colorID,
> papers.*
> FROM ( color
> INNER JOIN colorLOOKUP ON color.colorID = colorLOOKUP.colorID)
> INNER JOIN papers ON colorLOOKUP.itemNUM = papers.ITEM_NUM
> WHERE color.colorCAT = '#url.color#'
> OR color.colorNAME = '#url.color#'
> ORDER BY papers.PA_CATEGORY ASC
> </cfquery>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:206556
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
