SQL help - Using DISTINCT and INNER JOIN

[csiwa28]

Joining 2 tables, I use the DISTINCT keyword to eliminate redundant information however it doesn't seem to work. Does the DISTINCT keyword only work on one table at a time and not with joined tables?

SELECT DISTINCT company_companyname, product_productname FROM company INNER JOIN product ON company_companyid = product_companyid

 

[vasah20]

distinct will get all the distinct combinations of company_companynames and product_product_name... so that might be where your problem is stemming from:

for ex: it will return
companyA productA
companyB productA
companyB productB
companyA productB

I'm thinking that you only want the distinct companies and products... so you would want something like
companyA productA
companyB productB

is this correct?
leo

 

[csiwa28]

Sorry, it was the wrong SQL statment that I copied and pasted.

SELECT DISTINCT company_companyname, product_productname FROM product INNER JOIN company ON product_companyID = company_companyID WHERE product_productID =" & Request.QueryString("ProductID&quot & " ORDER BY company_companyname"

 

[vasah20]

so i'm thinking that you want a company name, and then all the products associated with that company, right?

eg:
companyA
product1
product2
product3

companyB
product2
product3

is this right?

if so - data shaping might be something that you want to look at - it'll help you solve your problem.

see

http://www.4guysfromrolla.com/webtech/092599-1.shtml

if you don't want to data shape, then you might want to consider grouping the results by companyname

eg:
SELECT DISTINCT company_companyname, product_productname FROM product INNER JOIN company ON product_companyID = company_companyID WHERE product_productID =" & Request.QueryString("ProductID&quot & " GROUP BY company_companyname"

i'm not sure if i'm helping at all, because I don't really think i know what you want your resulting recordset to be.

i hope i'm helping at least a little
leo