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

    Unanswered: DISTINCT Selection problem!

    Hi guys/gals

    Can anyone help with this Select statement please.
    For some reason, when i use DISTINCT, i'm still getting multiple ManufacturerID's



    Code:
    SELECT DISTINCT 
                          TOP (100) PERCENT dbo.ClientManufacturers.ManufacturerID, dbo.ClientManufacturers.ClientID, dbo.Manufacturers.Manufacturer, 
                          dbo.Products.Accessory, dbo.Products.TestLead
    FROM         dbo.ClientManufacturers INNER JOIN
                          dbo.Manufacturers ON dbo.ClientManufacturers.ManufacturerID = dbo.Manufacturers.ManufacturerID INNER JOIN
                          dbo.Products ON dbo.Manufacturers.ManufacturerID = dbo.Products.ManufacturerID
    WHERE     (dbo.ClientManufacturers.ClientID = 0) AND (dbo.ClientManufacturers.ManufacturerID <> 9) AND (dbo.ClientManufacturers.ManufacturerID <> 18) AND 
                          (dbo.ClientManufacturers.ManufacturerID <> 19) AND (dbo.Products.Accessory = 1) OR
                          (dbo.ClientManufacturers.ClientID = 0) AND (dbo.ClientManufacturers.ManufacturerID <> 9) AND (dbo.ClientManufacturers.ManufacturerID <> 18) AND 
                          (dbo.ClientManufacturers.ManufacturerID <> 19) AND (dbo.Products.TestLead = 1) OR
                          (dbo.ClientManufacturers.ClientID = 0) AND (dbo.ClientManufacturers.ManufacturerID <> 9) AND (dbo.ClientManufacturers.ManufacturerID <> 18) AND 
                          (dbo.ClientManufacturers.ManufacturerID <> 19) AND (dbo.Products.Accessory = 1)
    ORDER BY dbo.Manufacturers.Manufacturer
    Attached Thumbnails Attached Thumbnails sg.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, yes, of course

    DISTINCT means that the entire row, consistnig of all columns in the SELECT clause, is unique

    since a manufacturer has multiple products, of course a manufacturer will show up more than once

    if you want only distinct manufacturers, don't select any product columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also worth pointing out that you shouldn't be using "TOP 100 PERCENT" as there is simply no need.

    If you're using this in combination with ORDER BY to fudge a view in to retaining your specified order, don't. It won't work properly and can actually decrease performance.
    George
    Home | Blog

Posting Permissions

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