asked on

SQL Query

I have a table, tblsuggestedproducts, that has productid, suggestedproductid. Both of these fields relate to the primary key, productid, in tblproducts

tblsuggestedproducts looks like this
productid suggestedproductid
1 3
1 4
1 7
2 2
2 3

I want to return a query that gives me the related data in tblproducts for both the tblsuggestedproducts.productid and the tblsuggestedproducts.suggestedproductid....any help would be appreciated.

dougaug

Try this query:

select prod.product_name,
prod.product_description,
prod2.product_name,
prod2.product_description
from #tblproducts prod inner join #tblsuggestedproducts sugprod on (prod.product_id = sugprod.product_id)
inner join #tblproducts prod2 on (prod2.product_id = sugprod.suggestedproduct_id)

ASKER CERTIFIED SOLUTION

dougaug

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joriszwaenepoel

SELECT T2.*, T3.*
FROM tblsuggestedproducts T1
INNER JOIN tblproducts T2 ON T1.productid = T2.ID
INNER JOIN tblproducts T3 ON T1.suggestedproductid = T3.ID

cartch2008

ASKER

Brilliant dougag...now, what if I also wanted to join the products table to another table....ie: I have a Vendor table. tblproducts has a vendorid in it that links to the vendorid in the vendortable

cartch2008

ASKER

thanks...would like to know how I can join another table to tblproducts though

dougaug

Try this:

select prod.product_name,
prod.product_description,
vend.vendor_name,
prod2.product_name,
prod2.product_description,
vend2.vendor_name
from tblproducts prod inner join tblsuggestedproducts sugprod on (prod.product_id = sugprod.product_id)
inner join tblproducts prod2 on (prod2.product_id = sugprod.suggestedproduct_id)
inner join tblvendor vend on (vend.vendor_id = prod.vendor_id)
inner join tblvendor vend2 on (vend2.vendor_id = prod2.vendor_id)