Get Unique recordset with INNER JOINS

[SonOfNels]

I am confused about how to fix this problem.
First my query:
<CFQUERY NAME="rndpick" DATASOURCE="absi">
SELECT DISTINCT catfit.prodid, modcats.catid, modcats.cat, prod.modlid, prod.prodtype, prodmat.prtnum, prodmat.brand, prodmat.modl, prodmat.features, prodmat.pic, prodmat.dcp, prodmat.prc, prodmat.shp
FROM ((catfit INNER JOIN prodmat ON catfit.prodid=prodmat.prodid) INNER JOIN ModCats ON Catfit.catid = ModCats.catid) INNER JOIN prod ON prod.modlid=catfit.modlid
WHERE ((Catfit.slsid)=1)
</cfquery>

Now I know that the DISTINCT would not work in this case becuase it looks at each records total value (all columns in query). I had to try. I have also tried to use the GROUP BY to no avail.

The Question: How can I use an SQL query (based on what I have above) to give my a unique recordset using the catfit.prodid column as the filter?

I have read that you can use two queries to pull this off but could not find a good example of it so I could make it work. The first qurey to get the distinct values for the column I need and the second query to get the rest of the vaules. Not sure how to do this in practice?

Your help is greatly appreciated!

[Newsgroup_User]

if you want to search for a specific prodid, add it to your WHERE
clause. i do not see it there now...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[SonOfNels]

Actually what I need: I have a list of products that could be in the database several times. The catfit.prodid column is the column that will have like values and the rest will be unique in that table. So what I want to do is get just the unique values for the catfit.prodid column.

[Dan_Bracuk]

quote:

---

Originally posted by: SonOfNels
Actually what I need: I have a list of products that could be in the database several times. The catfit.prodid column is the column that will have like values and the rest will be unique in that table. So what I want to do is get just the unique values for the catfit.prodid column.

---

Just out of curiousity, what are you going to do with them once you have them?

[SonOfNels]

Simply put, I am going to display them. I have a page that displays a random list of products and on the landing page the category that they are in is not relevant because I just want to show them a good mix of products every time the page is loaded. On other pages the category matters for searching simplicity. I want to to get this accomplished with the query because it took me some time to figure out how to display them randomly (with much help).

[paross1]

You could use Q-of-Q (query of query) to "filter" your first result set. Or, you could write a second database query that only selects the unique fields of interest. Q-of-Q has one advantage because it only requires a single database "hit", but has limitations because of its simple set of SQL commands, so if you require more robust SQL, you may have to use a second database query.

Phil

[Dan_Bracuk]

select distinct product and no other fields
from etc

[SonOfNels]

Will that work if I want to display information from other columns in different tables?
So if I do what you suggest can I:

<cfoutput>
#catfit.prodid#<BR>
#prodmat.modlid#<BR>
#prodmat.brand#
ect.
</cfoutput>

[Dan_Bracuk]

quote:

---

Originally posted by: SonOfNels
Will that work if I want to display information from other columns in different tables?
So if I do what you suggest can I:

<cfoutput>
#catfit.prodid#<BR>
#prodmat.modlid#<BR>
#prodmat.brand#
ect.
</cfoutput>

---

You can run your original query to get the data you need. As mentioned by paross1, you can do a Q of Q to get the distinct product names.

However, if a product has two categories, and you want to show both, then maybe you should look into the group attribute of cfoutput.

[SonOfNels]

>>You can run your original query to get the data you need. As mentioned by paross1, you can do a Q of Q to get >>the distinct product names.

Can you show me an example?

>>However, if a product has two categories, and you want to show both, then maybe you should look into the >>group attribute of cfoutput.

With this particular page I am just concerned with the product not the categories, so all I want is for one product to show up once regardless if it is in the db 2 or more times.

Thank you for all of your help!!!

[Newsgroup_User]

Dan Bracuk wrote:
> select distinct product and no other fields
> from etc

OR

SELECT DISTINCT(PRODUCT), other, fields

Buy using the parenthesis you can tell the distinct clause to only
consider those columns for the purpose of being distinct. Just be aware
that this can lead to strange results if the database has to make a
guess from two or more related records to full the other fields.