sql - select on one result from the query -

i'm trying query few tables in our db contains product data. mssql 2005 database. problem returning multiple rows 1 product when need produce 1 row per product. blow query , results i'm using.

select      productitem.sku, productitem.title, productitem.short_desc, productitem.long_desc, productitem.active, productitem.product_item_id,                      productcategory.category_desc, productcategorymap.product_id, productcategory.active activecat, product.adwords_label,                      productitem.item_price, productitem.sale_price          product inner join                         productcategorymap inner join                         productcategory on productcategorymap.product_category_id = productcategory.product_category_id on                          product.product_id = productcategorymap.product_id full outer join                         productitem on product.product_key = productitem.sku      (productitem.active = 1) , (productcategory.active = 1) 

this returns following results:

i know problem occurs because product resides in multiple categories, don't need every category in, 1 of them. ideally 1 product every row returned

i can't figure out how make query achieve though.

can me out please?

at first glance, take category columns out , add distinct. you've asked "category" can categories.

also:

• the changes full outer join inner
• get product_id product
• the activecat column implied clause anyway

i've tweaked clarity , added aggregate 1 category

select    pi.sku, pi.title, pi.short_desc, pi.long_desc,     pi.active, pi.product_item_id,     pi.item_price, pi.sale_price,      min(pc.category_desc),    p.product_id,    1 activecat, --implied filter     p.adwords_label      productitem pi     inner join     product p on p.product_key = pi.sku     inner join     productcategorymap pcm on p.product_id = pcm.product_id     inner join     productcategory pc on pcm.product_category_id = pc.product_category_id    (pi.active = 1) , (pc.active = 1) group    pi.sku, pi.title, pi.short_desc, pi.long_desc,     pi.active, pi.product_item_id,     pi.item_price, pi.sale_price,     p.product_id,    p.adwords_label 

edit: can tidy more apply deal no categories if changed outer apply

select    pi.sku, pi.title, pi.short_desc, pi.long_desc,     pi.active, pi.product_item_id,     pi.item_price, pi.sale_price,      pc2.category_desc,    p.product_id,    pc2.active activecat,     p.adwords_label      productitem pi     inner join     product p on p.product_key = pi.sku     cross apply     (     select top 1         pc.category_desc, pc.active             productcategorymap pcm         inner join         productcategory pc on pcm.product_category_id = pc.product_category_id            p.product_id = pcm.product_id , pc.active = 1     order         pc.category_desc     ) pc2    pi.active = 1