DISTINCT keyword in SQL server 2005

Question

Hi I have written this query in sql server 2005 but it still will show the name of products that are the same!!thanks

SELECT  DISTINCT ProductName 
FROM Products,Suppliers 
WHERE Products.SupplierID = Suppliers.SupplierID AND Fax IS NULL

for example I have two product names that are the same 'Chei' instead of returning one 'chei' it will return both of them

Answer 1

What you have posted should work fine.

Is it possible you're selecting other stuff like this?

SELECT  DISTINCT ProductName, ProductId 
FROM Products,Suppliers 
WHERE Products.SupplierID = Suppliers.SupplierID AND Fax IS NULL 

Alternatively, you should be writing it like this (but this won't help you if you are selecting multiple columns).

SELECT  DISTINCT ProductName 
FROM Products 
INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID 
WHERE Fax IS NULL 

If you are confused as to why selecting multiple columns wouldn't work:

SELECT  DISTINCT ProductName, ProductId
FROM Products 
INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID 
WHERE Fax IS NULL 

Just think about it a moment as follows:

Products table:
ID  Name
1   Test
2   Test
3   Other
4   Random

You want:

Mixed:
ID  Name
4   Random
3   Other
?   Test

How should it pick that 'id' for Test in the Distinct mixed table? As such, it gives you all DISTINCT combinations of all the data you've asked for.

i.e.

Mixed:
ID  Name
4   Random
3   Other
2   Test
1   Test

Answer 2

• If you use EXISTS, you shouldn't need DISTINCT
• ANSI PADDING means you may have trailing spaces: but why is ProductName repeated?
• Is your post the actual query?
• Use correct JOIN syntax

Options:

SELECT --DISTINCT if you need RTRIM
    P.ProductName  --RTRIM(ProductName, '')
FROM
    Products P
WHERE
    EXISTS (SELECT *
        FROM
           Suppliers S
        WHERE
           P.SupplierID = S.SupplierID
           AND
           Fax IS NULL -- belongs to Suppliers?
             )