Select from sub queries - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select from sub queries

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2010-04-01 : 10:40:36

I am trying to select golfers names from subqueries within a main query. For some reason I keep on getting errors. My syntax looks correct. Can anyone help?


SELECT 		t.teamname, 
			t.stableford, 
			t.ranker, 

			(select g.firstname, g.lastname from golfers g where g.golfid = t.golfer1 ) as golfer1, 
			(select g.firstname, g.lastname from golfers g where g.golfid = t.golfer1 ) as golfer2,
			(select g.firstname, g.lastname from golfers g where g.golfid = t.golfer1 ) as golfer3,
			(select g.firstname, g.lastname from golfers g where g.golfid = t.golfer1 ) as golfer4


FROM		teamStanding t
ORDER BY	t.ranker DESC

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 10:46:07

You can select only one column in subquery:


(select g.firstname + g.lastname as [name] from golfers g where g.golfid = t.golfer1 ) as golfer1, 
(select g.firstname + g.lastname as [name] from golfers g where g.golfid = t.golfer1 ) as golfer2,
...

Harsh Athalye
http://www.letsgeek.net/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 10:48:11

Or more elegant solution would be to join two tables.

Harsh Athalye
http://www.letsgeek.net/

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2010-04-01 : 10:57:11

This worked!! I had no clue THank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 11:06:31

But it looks like golfer1 to golfer4 will bring always the same name because there isn't any difference in the 4 subselects.

No, you're never too old to Yak'n'Roll if you're too young to die.

PackRat
Starting Member

26 Posts

Posted - 2010-04-01 : 14:44:05

Assuming that teamStanding has columns golfer1 - golfer4 adding the join syntax the query would come out;


SELECT 		
	t.teamname, 
	t.stableford, 
	t.ranker, 
	g1.firstname + g1.lastname as [golfer1],
	g2.firstname + g2.lastname as [golfer2],
	g3.firstname + g3.lastname as [golfer3],
	g4.firstname + g4.lastname as [golfer4]
FROM teamStanding t
	left join golfers g1 on t.golfer1=g.golfid
	left join golfers g2 on t.golfer2=g.golfid
	left join golfers g3 on t.golfer3=g.golfid
	left join golfers g4 on t.golfer4=g.golfid
ORDER BY t.ranker DESC

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 14:49:23

then also logically thinking shouldnt they bring the same value for golfer1 - golfer4 as you're joining with same id value and retrieving their firstname,lastname info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 17:21:11

Correct. That's what I mean.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources