Correlation name/nested SELECT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
sunegtheoverlord
Forum Commoner
Posts: 28
Joined: Thu Feb 18, 2010 5:02 am

Correlation name/nested SELECT

Post by sunegtheoverlord »

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?
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Correlation name/nested SELECT

Post by aravona »

Code: Select all

SELECT EnqRef ClientRef CmpName
FROM Enquiry
INNER JOIN Cmp
ON Enquiry.CmpRef1 = Cmp.CmpRef
This should work, as a basis and get you what you want for the first item. I'm not 100% sure if you can have multiple ON's in a join. But if this works you could always try having more than one.

I'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
User avatar
sunegtheoverlord
Forum Commoner
Posts: 28
Joined: Thu Feb 18, 2010 5:02 am

Re: Correlation name/nested SELECT

Post by sunegtheoverlord »

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.
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Correlation name/nested SELECT

Post by aravona »

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 :)
Post Reply