SQL Query Prob

Question

Post reply

SQL Query Prob

tadhg88

Ten Centuries

Points: 1091
More actions
April 24, 2007 at 7:48 am

#177529

Just a quick question on this sql query if anyone can help I would really appreciate it basically squad player mapping maps a person to different national and international squads but this query needs to display national clubs only but some players are ONLY in international squads so im just wondering if its possible to display null for these players clubs but actually have them displayed thanks in advance

Select p.id as playerId,
IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',
actualClub.Name as 'Club',
actualClub.Type as 'International Team',
s.ID as 'SquadID'

From ScoutingReport as sr
Join Person as p on p.Id = sr.ScoutedPersonID
Left Join SquadPlayerMapping as spm on spm.PlayerID = p.ID
Left Join Squad as s on s.Id = spm.SquadId
Left Join Club as actualClub on actualClub.Id = s.ClubId

Where sr.Recommendation = 1
And sr.ReportDate Between '07/01/2006' And '04/25/2007'
And sr.Position = 12
And spm.DateEntered <= GetDate()
And spm.DateLeft is NULL
And spm.Type = 0 -- contracted players
--And actualClub.Type = 0 -- not international

Group By p.ID, p.FirstName, p.MiddleName, p.LastName, sr.Recommendation, sr.Position,
actualClub.Name, sr.ScoutedPersonID, actualClub.Type, s.id
Order by sr.ScoutedPersonID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try this

SELECT ...

From ScoutingReport as sr

Join Person as p on p.Id = sr.ScoutedPersonID

Left Join SquadPlayerMapping as spm on spm.PlayerID = p.ID

Left Join Squad as s on s.Id = spm.SquadId

Left Join (SELECT <columns> FROM Club WHERE Type=0) actualClub on actualClub.Id = s.ClubId

Where sr.Recommendation = 1

And sr.ReportDate Between '07/01/2006' And '04/25/2007'

And sr.Position = 12

And spm.DateEntered <= GetDate()

And spm.DateLeft is NULL

And spm.Type = 0 -- contracted players

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Burrows SSC Guru Points: 65143 More actions · Answer 2

I would move

And actualClub.Type = 0

to the LEFT JOIN, ie

LEFT JOIN Club actualClub ON actualClub.Id = s.ClubId AND actualClub.Type=0

Far away is close at hand in the images of elsewhere.
Anon.

tadhg88 Ten Centuries Points: 1091 More actions · Answer 3

tadhg88

Ten Centuries

Points: 1091

April 25, 2007 at 4:13 am

#702323

nice one thanks alot man