Using DISTINCT for some but not others in 1 query

[fidibidabah]

I feel really stupid.

That doesn't happen too often..

but I feel really stupid.

SQL is completely kicking my face in right now.
So to get on with my question... (I'm kinda new to all this)

I have a gigundormous databse. I understand that if I do something like
"SELECT DISTINCT CATAGORY_NAME PRODUCT_NAME" that it will not work, because
although there are duplicats in CATAGORY_NAME, obviously the PRODUCT_NAME's are
all going to be unique, therefore it will retrive DISTINCT for neither. This
being true, I need someway to select CATAGORY_NAME distinctly, and also SELECT
PRODUCT_NAME. Why not use two different queries? Good question, the reason is
because I need to <cfoutput query=""> to get that loop effect going.. and if I
can only output one query at a time (CF doesn't support nested queries, or
atleast that's what it told me when I tried to do it) then I need to find
another way to do it.

If it'll help, instead of telling you I need to do this selecting, I'll simply
tell you what I'm trying to do and see if you can help me there (if not, the
answer to the first question would be just STUPENDIOUS!).

I have a ginormous table. (important) Fields include "CatName" "Brand" and
"ProductName". I need to be able to display it something like the following:

--CatName1---- --CatName2---- --CatName3----
---product 1--- ---product 1--- ---product 1---
---product 2--- ---product 2--- ---product 2---
---product 3--- ---product 3--- ---product 3---
---product 4--- ---product 4--- ---product 4---
---product 5--- ---product 5--- ---product 5---
-click for more- -click for more- -click for more-

I have the formatting for the table worked out. Basically it puts a <tr> when
currentrow mod 4 eq 1 and it puts a </tr> when currentrow mod 4 eq 0. In other
words, it gives me 4 CatNames per row. I'm sure you can see where I'm running
into a problem. Since there are only about 40 catagories and 3500 products, if
the CatName is not unique, I'm going to get 3500 listings. But if I don't ALSO
select the ProductName in this same query, it's not going to loop, and only the
first set of 40 catagories is going to have products listed under it.

The 2nd problem I'm having is how to get the first 5 products (alphebetically)
under each CatName to display, although I figure I'll try this problem when I
come to it. I was thinking a GROUP BY CatName ORDER BY ProductName and then do
a little ditty to just pull the first 5.

Anyway, any light on any of these subjects would be wonderful. I think I'm way
over my head. I love you guys!

[The ScareCrow]

I don't think you need to consider "distinct" at all

If you do
SELECT CATAGORY_NAME, PRODUCT_NAME

Using

ORDER BY SELECT CATAGORY_NAME, PRODUCT_NAME

Then in the cfoutput query tag use the group attribute

<cfoutput query="queryName" group="CATAGORY_NAME">

The tricky part would be outputting as you want.

But you could also use 2 queries

<cfoutput query="query1Name">
#CATAGORY_NAME#
<cfloop query="query2Name">
<!--- need to check that the category name or id is the same --->
<cfif query1Name.CatID IS query2Name.CatID>
#PRODUCT_NAME#
</cfif>
</cfif>
</cfoutput>

Ken

[fidibidabah]

I see. I don't understand it even a little, but I'll play around with it.

I assume when you said "ORDER BY SELECT CATAGORY_NAME, PRODUCT_NAME" you ment
"ORDER BY CATAGORY_NAME, PRODUCT_NAME"? Cos I'm pretty sure you can't order by
select :) I also didn't know you could order by two different things (I guess
in that priority?), that's very interesting. Plus that cfloop thing I've never
heard of either. I'll start trying stuff and see what I come up with. Thanks :)

[The ScareCrow]

Yes, I forgot to delete the "SELECT" from the copy & paste.

You can have any valid column in the select list, you can also set the order
for each column (Asc or Desc)
In ms access and sql server you can also apply functions to them. Don't know
about other db's as I don't use them.

If you forget about the output for the moment, you can do this

Select catagory_name, product_name
From Categories Inner Join Products On categories.CatID = Products.CatID
Order By catagory_name, product_name

Then in cf

<cfoutput query="QueryName" group="catagory_name">
#category_name#
<br>
<cfoutput>
#product_name#<br>
</cfoutput>
</cfoutput>

Ken

[fidibidabah]

Hm, well this is interesting. Your two query thing worked out almost the way I
wanted.

The problem is that the first catagory is coming up fine with the list of
products under it, but the rest of the catagories are all coming up right, but
with the first catagories products under it! So i have a perfect catagory
listing, and then the same products under it for each one. To help you, let me
paste my exact code:

<cfquery name="cat" datasource="source">
SELECT DISTINCT CATNAME
FROM TABLE
ORDER BY CATNAME
</cfquery>

<cfquery name="product" datasource="source">
SELECT CATNAME, NAME
FROM TABLE
ORDER BY NAME
</cfquery>

those are the queries, and then the following is the ouput:

<table>
<cfoutput query="cat" group="catname">
<cfif cat.currentrow mod 4 eq 1>
<tr>
</cfif>
<td>
<div>
#catName#<br>
<cfloop query="product"><cfif cat.catName IS
product.catName> #name#<br></cfif>
</cfloop>
</div>
</td>
<cfif cat.currentrow mod 4 eq 0>
</tr>
</cfif>
</cfoutput>
</table>

I removed almost all the formatting and tag attributes (to poke at the code
easier). Any more help would be wonderful :( I feel I'm half way there tho!

[fidibidabah]

That QUERY look fancy... I'm just trying to understand it.. I thought you could
only join if you had multiple tables? Is that what "Inner Join" means? To join
things with comment attributes within the same table? That would be really
awesome. The part I'm confused about... your query there says From Categories
(which I assume is suppose to be the table name), and then 'cetagories.catID =
products.catID' but that doesn't make sense to me. I don't have a table called
"products". The categoryname and productname are both in the same table :(
Sorry, maybe I'm being dense here..

I really do appreciate your time Ken :)

[The ScareCrow]

I think you should do som research on db development, but the following should
get you going

<cfquery name="cat" datasource="source">
SELECT CATNAME, NAME
FROM TABLE
ORDER BY CATNAME, NAME
</cfquery>
<table>
<cfoutput query="cat" group="catname">
<cfif cat.currentrow mod 4 eq 1>
<tr>
</cfif>
<td>
<div>
#catName#<br>
<cfoutput>
#name#<br>
</cfoutput>
</div>
</td>
<cfif cat.currentrow mod 4 eq 0>
</tr>
</cfif>
</cfoutput>
</table>

Ken

[lther]

If I get your question right, I just had the same problem.

I tried, in your case:

SELECT DISTINCT CATAGORY_NAME, PRODUCT_NAME

And it seemed to choose only the first one as distinct. If you need two as
distinct and one as not distinct, I'm not sure... But unless I've got it
figured wrong in my case or it's not the way it's supposed to work, the line
above should choose the CATAGORY_NAME as Distinct and not PRODUCT_NAME.

I hope this helps.

[fidibidabah]

Thank you so much. I decided to deal with it a slightly different way which is
much easier on the brain. I really do appreciate all your help tho', I've
learned a lot from it. As I said, I'm a begining with all this stuff, but I'm
learning quick (knew nothing about MySQL, SQL language, PHP, or CFMX7.. nothing
at all, a week ago).

I do appreciate your help, and I think this app is going to turn out just fine.

Thanks!

[The ScareCrow]

Just thought I would clarify the "Distinct" keyword

This key word effects every column in the select list, so in the example given
here it would return rows with the distinct column pair. If there was 3
columns it would return the distinct tuples.

Ken