Complex Self Join

This is a very complex query and i have tried everything with no sucess.

I'm having 3 Tables,

Orders which is having fields like CustomerID, ManufactureID, MerchID :- These all ID Fields (around 6) are foreign key of Contacts and Address Tables.

Address Table is having AddrID(Primary Key), ShortName

Contacts is having ContID (Primary Key), AddrID (Foreign Key), PersonName

I want to retreieve info in a single query which can return a single row with following columns :-

OrderNo, CustomerName, ManufactureName, MerchName etc. (all 6 columns) by joining these 3 tables

can anybody help in this.

You will need to join each table with a unique alias.

For example:

SELECT OrderNo, CustomerName = cust.PersonName,
ManufactureName = manu.PersonName,......
FROM Orders or
JOIN Contacts cust ON cust.ContactID = or.CustomerID
JOIN Address custadd ON custadd.AddrID = cust.AddrID
JOIN Contacts manu ON manu.ContactID = or.ManufactureID
JOIN Address manuadd ON manuadd.AddrID = manu.AddrID

etc

I will warn you this will be relatively slow, if there are lots of records in Contacts.

Another way would be:

Select OrderNo,
CustomerName = (SELECT PersonName FROM Contacts cn WHERE cn.ContactID = or.CustomerID)
ManufactureName = (SELECT PersonName FROM Contacts cn WHERE cn.ContactID = or.ManufactureID)

FROM Orders or|||

Could you use one IDE (for example, Design Query in Editor in SQL Server 2005) to do this? You drag and drop your tables in the editor, and then you link them through key fields, and you pick the columns you are interested.

The query will be something look like this:

SELECT a.OrderNo, b.CustomerName, b.ManufactureName, c.PersonName as MerchName FROM Orders a INNER JOIN Contacts b ON a.CustomerID=b.ContID INNER JOIN Address c ON c.AddrID=a.AddrID

|||

Thanks for your response.

pls note i can also use seperate queries to extract each column, even that will do. Do you think that will be faster then joining all together..

|||It depends on the contacts table whether it will be faster or not. I have had experience in SQL 2000 where self joining 5 tables, worked fine, then the 6th make it take 4 times longer to return.

You will have to play with it and see what works for your situation.