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?
Correlation name/nested SELECT
Moderator: General Moderators
- sunegtheoverlord
- Forum Commoner
- Posts: 28
- Joined: Thu Feb 18, 2010 5:02 am
Re: Correlation name/nested SELECT
Code: Select all
SELECT EnqRef ClientRef CmpName
FROM Enquiry
INNER JOIN Cmp
ON Enquiry.CmpRef1 = Cmp.CmpRefI've not seen a layout like you've used before for sql. What SQL are you using? This should help with joins http://www.w3schools.com/SQl/sql_join.asp
- sunegtheoverlord
- Forum Commoner
- Posts: 28
- Joined: Thu Feb 18, 2010 5:02 am
Re: Correlation name/nested SELECT
Hi aravona,
thanks for the reply, yes that gives the first CmpName.
I think the problem lies in the columns in the SELECT statement. I need to somehow JOIN the two tables on CmpRef but reference them in the SELECT statement.
any ideas.....
btw, i'm using MS SQL 2000.
thanks for the reply, yes that gives the first CmpName.
I think the problem lies in the columns in the SELECT statement. I need to somehow JOIN the two tables on CmpRef but reference them in the SELECT statement.
any ideas.....
btw, i'm using MS SQL 2000.
Re: Correlation name/nested SELECT
Can you post a db layout and how you want it to layout on a page?
You may just have to do more than one query, looking at your first post I cannot find anything on the net using more than one ON. But I use mySQL 5.1.36 so
You may just have to do more than one query, looking at your first post I cannot find anything on the net using more than one ON. But I use mySQL 5.1.36 so