Query customers and suppliers from one table

[giedrius]

I did not follow textbook advice and put customers and suppliers into one table1 with fields partnerId,partnerName,partnerAdr.

I also have a products1 table with fields prodId,prodName,prodCustomerId,prodSupplierId. The last 2 fields refer to partnerId in table1.

I want to run a query to find out which products are supplied by which suppliers, i.e. I want to get a result like follows:

prodName, Customer Name, Suppplier Name

I do not know how to write an SQL to extract Customer Name and Supplier Name for a single prodName field.

I would be very grateful for any ideas.

Giedrius

 

[giedrius]

This is great elegant solution. Thanks a lot.

giedrius