Correlation name/nested SELECT
Posted: Wed Feb 24, 2010 5:30 am
Hello,
I'm totally new to SQL so be kind....
I've got a table called "Enquiry" as follows:-
EnqRef ClientRef CmpRef1 CmpRef2 CmpRef3
the CmpRef* columns all contain a number that references an entry in the Cmp table.
I want to write a SELECT statement to show me EnqRef ClientRef and CmpName1-3(from the Cmp table based on the CmpRef in the Enquiry table)
So far i've got this but it doesn't work....
SELECT
Enquiry.dbo.EnqRef
,Enquiry.dbo.ClientRef
,Cmp.dbo.CmpName
,Cmp.dbo.CmpName
,Cmp.dbo.CmpName
FROM (((Enquiry
INNER JOIN Cmp ON Enquiry.dbo.CmpRef1 = Cmp.dbo.CmpRef)
INNER JOIN Cmp ON Enquiry.dbo.CmpRef2 = Cmp.dbo.CmpRef)
INNER JOIN Cmp ON Enquiry.dbo.CmpRef3 = Cmp.dbo.CmpRef)
This only shows me the first CmpName 3 times.
I realise that the SELECT bit is asking for the same CmpName 3 times but i've just included it to show what i'm trying to acheive.
Do i need to nest a select statement?? or use correlation names?
I'm totally new to SQL so be kind....
I've got a table called "Enquiry" as follows:-
EnqRef ClientRef CmpRef1 CmpRef2 CmpRef3
the CmpRef* columns all contain a number that references an entry in the Cmp table.
I want to write a SELECT statement to show me EnqRef ClientRef and CmpName1-3(from the Cmp table based on the CmpRef in the Enquiry table)
So far i've got this but it doesn't work....
SELECT
Enquiry.dbo.EnqRef
,Enquiry.dbo.ClientRef
,Cmp.dbo.CmpName
,Cmp.dbo.CmpName
,Cmp.dbo.CmpName
FROM (((Enquiry
INNER JOIN Cmp ON Enquiry.dbo.CmpRef1 = Cmp.dbo.CmpRef)
INNER JOIN Cmp ON Enquiry.dbo.CmpRef2 = Cmp.dbo.CmpRef)
INNER JOIN Cmp ON Enquiry.dbo.CmpRef3 = Cmp.dbo.CmpRef)
This only shows me the first CmpName 3 times.
I realise that the SELECT bit is asking for the same CmpName 3 times but i've just included it to show what i'm trying to acheive.
Do i need to nest a select statement?? or use correlation names?