Select a single row from an inner join

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)

Select a single row from an inner join

Author

Topic

kory27
Starting Member

35 Posts

Posted - 2008-02-22 : 16:02:37

Hi. I am no expert to sql by any stretch, but i am getting a little better. This is what i have. Two tables, tblProduct and CategoryProductArt. tblProduct has my product information and i need to get a category number for each product, but only one. tblProductCategories has the categories but each product has multiple categories. So, i did a select inner join from the productcode(productNumber), which is the shared value to pull the data, but how can i either break the returned rows apart so that i can select only one or select only the lowest number (categories are int)?

here is my select query;

Select Category_ProductArt.CategoryNumber,ProductNumber
From Caetory_ProductArt InnerJoin
tblProduct on Category_ProductArt.ProductNumber = tblProduct.productCode

Any help is greatly appreciated. Thanks so much!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-22 : 16:22:36

Use an aggregate function (perhaps MIN) with a GROUP BY.

select cp.ProductNumber, min(cp.CategoryNumber) as CategoryNumber
from Caetory_ProductArt cp
inner join tblProduct p
on cp.ProductNumber = p.productCode
group by cp.ProductNumber

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kory27
Starting Member

35 Posts

Posted - 2008-02-22 : 16:32:35

Tara,

you are truly an sql goddess in my eyes for helping me so quickly! i ran it, so now can i make this an update function or should i make the results a table then run the insert from there?

thanks again!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-02-22 : 16:36:51

What would you like to update?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kory27
Starting Member

35 Posts

Posted - 2008-02-22 : 16:42:05

i would like to update the field productNavId on tblProduct. Basically, what i have here is a product feed that i am integrating into my system. my system keys off a one to many in the sense that each product has a parent nav, productNavId, that is then assigned to many different category areas. the feed is from a company that doesn't do it like that, but their navrelationship structure handles it, as does mine, but i have to give the product a parent. nahmean? so that was prob to much info, but i wanted to explain.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources