Question

Locked

Select Distinct with Multiple Columns SQL Server

By Meengla ·
Hi,
I have a SQL Server 2005 database in which 3 tables are joined together by prodid field.
Here is a query which gets me close but not quite close enough. In this query the Product Name (from the Products table) shows up 4 times IF there are 4 options related to that Product. May be that's inevitable as far as query is concerned but how can I then show the Product name only once on a page (it is ColdFusion) if there are 4 options while still showing the product only once if there is only 1 option (the default option of 'na' in the Products_options table.
Thanks!
----------------------------
SELECT
dbo.products.prodid,
dbo.products.name,
dbo.products.description,
dbo.products.image,
dbo.products_options.optionname,
dbo.products_options.price,
dbo.products_options.optionid
FROM
dbo.products_featured
INNER JOIN dbo.products ON (dbo.products_featured.prodid = dbo.products.prodid)
INNER JOIN dbo.products_options ON (dbo.products.prodid = dbo.products_options.prodid)

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Sounds like you need some more key words

by Slayer_ In reply to Select Distinct with Mult ...

Sounds like you need a group by clause, sounds like you want to group by your product ID

I think if you group by something it automatically assumes you want distinct records of that grouping.


On my more complicated queries I tend to just drop it in a GUI'd editor and then tweak the code manually.

Collapse -

Thank you.

by Meengla In reply to Sounds like you need some ...

Thank you, SinisterSlay. I ended up fixing the problem by using a 'group by' in ColdFusion Output for the query and then sub-querying within the output to get the Options for Products. I know, not the most efficient way but the database is small and I needed to move on!

Back to Networks Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums