SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Wizard
    Join Date
    Nov 2000
    Location
    Chico, Ca
    Posts
    1,125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Server TSQL Questions

    Here is my SQL:
    Code:
       
     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:
    Code:
       
     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
    "Happiness doesn't find you, you find happiness" -- Unknown
    www.chuckknows.com

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code:
    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

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by danfran
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •