January 31, 2008 at 7:11 am
Hi,
I am having problems writing this query with SQL Server 2000, i need to display a list of players dependent on date selected but i also need to display the same list of players whether or not they have a row for that date, i have managed to write some of the SQL the relevant joins are in bold. Originally these tables will be empty for the selected date and so i need to display nulls beside the player name etc so the user can enter data but when there is data for the date selected i need to display the data. Any help would be appreciated
Thanks in advance
Tim
Select IsNull(aop.FirstName+ ' ','') + IsNull(aop.LastName,'') as 'Name',
acss.Name as 'Age',
aar.AttendanceID as 'Status',
Notes, ses.Date
From AcademyShadowSquadPlayerMapping spm
Left Join AcademyOtherPlayer aop on aop.ID = spm.PlayerID
Left Join AcademyShadowSquad acss on acss.ShadowSquadID = spm.SquadID
Left Join AcademySession ses on aar.AcademySessionID = ses.ID And ses.Date = '02/01/2008'
Left Join AcademyAttendanceRecord aar on aar.PlayerID = aop.ID
Where ((spm.SquadID = 3) or (-1 = 3))
January 31, 2008 at 7:20 am
Please will you post DDL in the form of CREATE TABLE statements and sample data in the form of INSERT statements, together with the results you expect. How is the query you have written not providing the results you require?
John
January 31, 2008 at 8:54 am
Do you need left joins to all those tables? Or is it just one that might be missing rows?
Also, pls post the DDL as mentioned by John.
January 31, 2008 at 9:02 am
What is up with having this in your WHERE clause?
(-1 = 3)
That's always FALSE - so why include it?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 31, 2008 at 9:23 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply