Distinct and Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct and Joins

Author

Topic

curry
Starting Member

14 Posts

Posted - 2008-10-07 : 09:30:03

Hi

I have an issue with the following statement

select products.productid, products.name, tbl_media.media_name
from products
inner join tbl_media on products.productid = tbl_media.productid
where products.catcode=26
order by products.productid asc

Returns

ID---Name---Image
575 Widget Widget_img1
575 Widget Widget_img2
575 Widget Widget_img3

But I need to put a single record for each product, not a single record for each media_name.

ID---Name---Image
575 Widget Widget_img1

I've tried SELECT DISTINCT but the join messes that up. I think I need to use a subquery but I'm still struggling with it.

Thank you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 09:37:55

[code]select products.productid, products.name, min(tbl_media.media_name) as media_name
from products
inner join tbl_media on products.productid = tbl_media.productid
where products.catcode=26
group by products.productid, products.name
order by products.productid asc[/code]

curry
Starting Member

14 Posts

Posted - 2008-10-07 : 09:52:34

Thank you sir.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources