Is this a possible select statement scenario? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is this a possible select statement scenario?

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2007-12-12 : 16:36:31

I have two tables.

tblAthletic
athleticID int,
athleticType nvarchar(50)

tblUserAthletic
userID int,
athleticID int,
hasGear bit,
Skill tinyint,

Id like to bring back every single result in tblAthletic and for each of those results, if tblUserAthletic has an entry for that athleticID based on the userID then bring that databack with the correpsonding row.

eg.

1,Soccer,skillx,true
2,Lacrosse,skillx,false
3,Baseball,null,false

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 16:41:30

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

robc
Yak Posting Veteran

60 Posts

Posted - 2007-12-12 : 17:32:54

Thanks Peso.

is that the only way. Is it possible to get the results returned in a table rather than a comma delimitted string? I ask this because i'm binding it to a gridview control (asp2.0).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 17:39:59

You can do a CROSSTAB/PIVOT query.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210 has some examples of dynamic CROSSTAB/PIVOT queries.

E 12°55'05.25"
N 56°04'39.16"

robc
Yak Posting Veteran

60 Posts

Posted - 2007-12-12 : 19:18:05

Hi Peso,

I had a look through the articles you posted. I played arond with the cross tabs SP but I'm still a bit confused. I think these crosstab examples might not be what im looking for. Or it's possible I just dont understand them. Because I'm only bringing this data back for 1 user at a time i'm looking to get my result set like so.

AthleticID AthleticType Skill hasGear

1 CRICKET NULL NULL
2 FOOTBALL 5 1
3 SOCCER 4 0

robc
Yak Posting Veteran

60 Posts

Posted - 2007-12-12 : 21:21:59

Well, I ended up trying out a Union and it seems to do the trick.

I'm not sure if it's efficient but it works.

SELECT a.athleticID,athleticType,rating,hasGear 
FROM tblAthletic a LEFT OUTER JOIN tblUserAthletic ua ON a.athleticID = ua.athleticID
UNION select ca.athleticID,athleticType,rating,hasGear from tblActorAthletic tbga INNER JOIN
tblAthletic ca ON tbga.athleticID = ca.athleticID
WHERE userID = 14

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-12 : 21:35:28

is this what you want ?

SELECT	a.athleticID, a.athleticType,
	u.hasGear, u.Skill
FROM	tblAthletic a
	INNER JOIN tblUserAthletic u
	ON	a.athleticID	= u.athleticID

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-12 : 21:36:12

if not, please post some sample data and the corresponding expected result

KH
[spoiler]Time is always against us[/spoiler]

robc
Yak Posting Veteran

60 Posts

Posted - 2007-12-12 : 22:35:32

Thanks Khtan,

But the union gave me what I wanted. I just wanted to bring back every result in the tblAthletic and for each of those athleticTypes list that particular users skill in it, and if they didn't have one recorded it would just return null.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources