nested select-join statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

nested select-join statements

Author

Topic

brucezepplin
Starting Member

15 Posts

Posted - 2011-07-20 : 04:59:14

Hi, I'm having trouble sorting out a nested select statements joined together:


SELECT * FROM
(SELECT DISTINCT NAME
FROM NAME_DB
WHERE NAME = 'HOMER'
) A

JOIN 

(SELECT DISTINCT NAME
FROM NAME_DB
WHERE NAME = 'HOMER'
) B

JOIN  
(SELECT DISTINCT NAME
FROM NAME_DB
WHERE NAME = 'HOMER'
) C

ON A.NAME = B.NAME
AND A.NAME = C.NAME
AND B.NAME = C.NAME

the error statement I am getting is sql0104n: an unexpected token "(SELECT DISTINCT NAME FROM NAME_DB" was found following " ) B JOIN " expected tokens may include <space>

do not worry about the field names etc etc, this is just a simplified version of what I am working on - it's the logic that doesnt appear to be working.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-20 : 05:15:01

I suspect you are coming from Access or other database application.
Learn ANSI style joins. Try this

SELECT		*
FROM		(
			SELECT DISTINCT	NAME
			FROM		NAME_DB
			WHERE		NAME = 'HOMER'
		) AS a
INNER JOIN	(
			SELECT DISTINCT	NAME
			FROM		NAME_DB
			WHERE		NAME = 'HOMER'
		) AS b ON b.NAME = a.NAME
INNER JOIN	(
			SELECT DISTINCT	NAME
			FROM		NAME_DB
			WHERE		NAME = 'HOMER'
		) AS c ON c.NAME = a.NAME

N 56°04'39.26"
E 12°55'05.63"

brucezepplin
Starting Member

15 Posts

Posted - 2011-07-20 : 05:24:43

that worked like a charm. thanks very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources