DISTINCT trouble 1

[PhatH]

EX: I have 3 tables, Products, Manufacturers and ProductType.
In the tblProducts containing both ManuID and ProdTypeID that links to the other two tables.
When I call to display ProdTypeNames that associate with a particular Manufacture, the tblProducts might have the products that are the same Manufacture and ProdType, and I don't want to show the ProdTypeName more than once.

by using sql2000,

sql = "SELECT DISTINCT tblProductType.ProdTypeName, tblProducts.ProductID, tblProducts.ManuID FROM tblProducts, tblProductType WHERE tblProducts.ProdTypeID = tblProductType.ProdTypeID and tblProducts.ManuID = '" & request.querystring("ManuID") & "'"

Could you tell me why the DISTINCT tblProductType.ProdTypeName is not working?

 

[checkai]

you're grabbing distinct productID and manuID right now.

 

[SQLBill]

DISTINCT applies to all the columns being selected.

For example:

col1 col2 col3
1 a Ann
1 a Adam
2 b Bill
2 b Bill

SELECT DISTINCT col1, col2, col3
returns:
col1 col2 col3
1 a Ann
1 a Adam
2 b Bill

Those three rows are DISTINCT. The fourth row was a duplicate so it was eliminated.

SELECT DISTINCT col1
returns
col1
1
2

SELECT DISTINCT col1, col2
returns
col1 col2
1 a
2 b

-SQLBill

 

[PhatH]

Is there a way to isolate which data I want to DISTINCT in one SQL query?

 

[amorous]

This thread might give you some idea.

http://www.tek-tips.com/viewthread.cfm?SQID=829576&SPID=183&newpid=183&page=1

-VJ

 

[PhatH]

Thank amorous,

The link you gave me was almost the same as my problem. As same as that person, I don't understand half of the SQL query. I tried to modify that final query to match to my requirement but nothing seems to work for me yet.

I got to move on to something else now. But DO want to see anyone else with different approaches... Thank you all!!!