SQL Server TSQL Questions

[Chuckie]

Here is my SQL:

   
 SELECT Products.ProductID, Products.Name, Categories.CategoryID, Categories.Name, Products.Description, ImageName, KEY_TBL.RANK
   FROM Categories
   Inner
   	join Category_Product
   	on Categories.CategoryID = Category_Product.CategoryID
   inner join Products 
   	on Category_Product.ProductID = Products.ProductID
   INNER JOIN FREETEXTTABLE(dbo.Products, *, 'soils barks') AS
   KEY_TBL ON Products.ProductID = KEY_TBL.[KEY]

My Result set:

   
 18	Custom Topsoil 6	Topsoil and Blended Soils	A blend of mulch, sand and soil. Good for raised planter beds or as a planting mix.	 080205100513.jpg	88
 19	Bonsai Mix	6 Topsoil and Blended Soils	Special soil mix recommended for Bonsai plantings.	 080205100306.jpg	118
 17	Topsoil	6 Topsoil and Blended Soils	Rich clean loam topsoil from Chico’s best soil areas. Use for planting, grading, mounds and backfill.	 080205103141.jpg	88
   15	Nitro-Humus 8	Misc. (Weed Fabric, Fire Rings)	A fine ground wood chip blended with rice hulls and decomposed manure and treated with added nitrogen fertilizer. For use as a soil amendment or top dressing after lawn seeding.	 020205114658.jpg	35
 15	Nitro-Humus	6 Topsoil and Blended Soils	A fine ground wood chip blended with rice hulls and decomposed manure and treated with added nitrogen fertilizer. For use as a soil amendment or top dressing after lawn seeding.	 020205114658.jpg	35
 14	Playground Bark 4	Decorative Bark	A cedar wood chip primarily used for cushioning under playground equipment. Government-approved as an under-layment.	 020205114630.jpg	64
   16	ValleyGold Compost 8	Misc. (Weed Fabric, Fire Rings)	A turkey/chicken manure based compost. Rich dark color, used for soil amendment and planting mix. Can also be used as top dressing after lawn seeding.	 020205114706.jpg	35
 16	ValleyGold Compost 6	Topsoil and Blended Soils	A turkey/chicken manure based compost. Rich dark color, used for soil amendment and planting mix. Can also be used as top dressing after lawn seeding.	 020205114706.jpg	35
 69	Small Bark	4 Decorative Bark	Decorative mulch used in planter beds to retain soil moisture and inhibit weed germination. 020205114639.jpg	118
 72	Mini Bark	4 Decorative Bark	Decorative mulch used in planter beds to retain soil moisture and inhibit weed germination. 020205114648.jpg	118
 129	Medium Bark	4 Decorative Bark	Decorative mulch used in planter beds to retain soil moisture and inhibit weed germination. 020205114716.jpg	118
 131	Walk on Bark 4	Decorative Bark	Decorative mulch used in planter beds to retain soil moisture and inhibit week germination. Stays in place on slopes better than other barks.	020205114733.jpg	213
 130	Shredded Cedar 4	Decorative Bark	Decorative mulch used in planter beds to retain soil moisture and inhibit week germination. Stays in place on slopes better than other barks.	020205114725.jpg	213
 173	Weed Barrier 8	Misc. (Weed Fabric, Fire Rings) Polypropylene fabric that is permeable to water and air. Used under rock and bark to inhibit weed germination. Very strong.	040205051526.jpg	49
 174	Weed Barrier Large 8	Misc. (Weed Fabric, Fire Rings) Polypropylene fabric that is permeable to water and air. Used under rock and bark to inhibit weed germination. Very strong. 	040205051511.jpg	49

Please note the bolded lines. I only need one of each. Distinct won't work because they are technically different values (they have different categories). The problem is those products are in two categories..

Can anyone suggest how to solve this problem?

Thanks,

Chuck

[danfran]

If a product of yours is in 4 categories, for example, and you do an INNER JOIN, it will appear 4 times in the result set. (I think you know this already.. hehe)

The following clause guarantees this.

inner join Products 
   	on Category_Product.ProductID = Products.ProductID

What you can do, if you absolutely need to keep category data, is to put the records into a #temp table, then discard the duplicates by your own design.

Else, remove the join to categories to eliminate duplicate products.

What exactly are you interested in showing? Products or a product-category distribution?

As well, you might be able to strip-out the category information by using GROUP BY and replacing the CategoryID with the CategoryCount. You would not be able to include any Category data except the category count. (without risking dupes)

Dan

[r937]

As well, you might be able to strip-out the category information by using GROUP BY and replacing the CategoryID with the CategoryCount. You would not be able to include any Category data except the category count. (without risking dupes)

excellent -- now we're proceeding in the right direction, but we aren't restricted to just a count of the (multiple) categories that a product is in

we could also select the highest category id, or the shortest category name, or the earliest category dateadded, or any similar criterion

many of these would require a subquery instead of a GROUP BY, and a temp table is often not necessary

the first step is to decide exactly which "one" piece of category data we want to show per product