Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Grouping sql results

    Can anyone help me with this one?
    I've come accross this same issue before, but im still not clear on the answer.

    I have a product results page that displays all the products from a category.
    sql statement below.

    If a product has more than one offer in the ClientOffers table, my result returns the productID multiple times (depending on how many offers that product has).

    How can i return just one productID regardless of the number of offers it has?

    I've seen GROUP being used but im not sure how or if this is the answer.

    ---my reason for wanting this---

    I'm using an IF THEN statement on my classic asp page to display an 'offers image icon' only if the product has offer/s.

    Thanks in advance....
    Andy


    Code:
    SELECT     TOP (100) PERCENT dbo.Categories.Category, dbo.ClientProducts.ClientID, dbo.Products.ProductID, dbo.Products.ManufacturerID, 
                          dbo.Products.Product, dbo.Products.Image, dbo.Products.Price, dbo.Categories.CategoryID, dbo.ClientOffers.OfferID
    FROM         dbo.Products LEFT OUTER JOIN
                          dbo.ClientProducts ON dbo.Products.ProductID = dbo.ClientProducts.ProductID LEFT OUTER JOIN
                          dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID LEFT OUTER JOIN
                          dbo.ClientOffers ON dbo.Products.ProductID = dbo.ClientOffers.ProductID AND dbo.ClientOffers.ClientID = dbo.ClientProducts.ClientID
    WHERE     (dbo.Categories.CategoryID = 15) AND (dbo.ClientProducts.ClientID = 0)

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try something like that:

    Code:
    SELECT
        dbo.Categories.Category, 
        dbo.ClientProducts.ClientID, 
        dbo.Products.ProductID, 
        dbo.Products.ManufacturerID, 
        dbo.Products.Product, 
        dbo.Products.Image, 
        dbo.Products.Price, 
        dbo.Categories.CategoryID, 
        COUNT(*) as QtyOffers
    FROM dbo.Products 
    LEFT OUTER JOIN dbo.ClientProducts 
        ON dbo.Products.ProductID = dbo.ClientProducts.ProductID 
    LEFT OUTER JOIN dbo.Categories 
        ON dbo.Products.CategoryID = dbo.Categories.CategoryID 
    LEFT OUTER JOIN dbo.ClientOffers 
        ON dbo.Products.ProductID = dbo.ClientOffers.ProductID AND 
           dbo.ClientOffers.ClientID = dbo.ClientProducts.ClientID
    WHERE (dbo.Categories.CategoryID = 15) AND 
          (dbo.ClientProducts.ClientID = 0)
    GROUP BY
        dbo.Categories.Category, 
        dbo.ClientProducts.ClientID, 
        dbo.Products.ProductID, 
        dbo.Products.ManufacturerID, 
        dbo.Products.Product, 
        dbo.Products.Image, 
        dbo.Products.Price, 
        dbo.Categories.CategoryID
    Hope this helps.

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    Hi imex

    Thank you for replying.
    It looks close but COUNT isn't correct.

    The results are showing 1 for products that either have one offer or no offers.
    Also the test product that has 3 offers is coming back with 5

    Any ideas?

    Fingers crossed
    Andy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyJay View Post
    The results are showing 1 for products that either have one offer or no offers.
    classic symptom of a classic error

    COUNT(*) always returns 1 in a LEFT OUTER JOIN when no rows match from the right table

    what you want is COUNT(dbo.ClientOffers.ProductID)

    also, since you have this --
    Code:
    WHERE (dbo.Categories.CategoryID = 15) AND 
          (dbo.ClientProducts.ClientID = 0)
    therefore the joins to dbo.Categories and to dbo.ClientProducts should be INNER JOINS
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2008
    Posts
    120
    You guys are amazing !

    Thank you so much.

    I now have the following which is working perfectly :-)

    To enlighten me r937, why did i need to change the Joins to INNER?

    Thanks again to you both

    Andy

    Code:
    SELECT     TOP (100) PERCENT dbo.Categories.Category, dbo.ClientProducts.ClientID, dbo.Products.ProductID, dbo.Products.ManufacturerID, 
                          dbo.Products.Product, dbo.Products.Image, dbo.Products.Price, dbo.Categories.CategoryID, COUNT(dbo.ClientOffers.ProductID) AS QtyOffers
    FROM         dbo.Products INNER JOIN
                          dbo.ClientProducts ON dbo.Products.ProductID = dbo.ClientProducts.ProductID INNER JOIN
                          dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID LEFT OUTER JOIN
                          dbo.ClientOffers ON dbo.Products.ProductID = dbo.ClientOffers.ProductID AND dbo.ClientOffers.ClientID = dbo.ClientProducts.ClientID
    WHERE     (dbo.Categories.CategoryID = 15) AND (dbo.ClientProducts.ClientID = 0)
    GROUP BY dbo.Categories.Category, dbo.ClientProducts.ClientID, dbo.Products.ProductID, dbo.Products.ManufacturerID, dbo.Products.Product, 
                          dbo.Products.Image, dbo.Products.Price, dbo.Categories.CategoryID
    ORDER BY dbo.Products.ProductID

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyJay View Post
    To enlighten me r937, why did i need to change the Joins to INNER?
    a LEFT OUTER JOIN always returns rows from the left table, with or without matching rows from the right table

    when there is no matching row from the right table, the columns in the result which come from the right table are set to NULL

    that's how outer joins work

    but then you specify a non-NULL requirement in the WHERE clause

    thus, all the unmatched rows are thrown away

    so you mought as well use an inner join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    p.s. maybe you can enlighten me why you feel it necessary to code TOP 100 PERCENT

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2008
    Posts
    120
    ah that makes perfect sense.

    For some reason MS SQL puts the TOP(100) percent in the statement.
    I remove it from my page code.

    :-)
    Andy

Posting Permissions

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