Help with simple query

Well complex for me

[Red_Eye]

I have 3 tables

Events
PK = EventID
DateofEvent

Attendees
PK AttendeeID
FK EventID
FK MemberID
Score
etc

Member
PK MemberID
FirstName
LastName
etc

There is one event a month members use the site to let the organiser know if they are attending the event. Once the event has happend their score is entered. Memeber do not attend every event. But what I want to do is query the database and get the scores for a member for each month, if they didn't attend the event I still want to see a month but with zero for the score
eg.

Jan - 22 - Event1
Feb - 22 - Event2
March - 23 - Event3
April - 15 - Event4
May - 26 - Event5
June - 29 - Event6
July - 12 -Event7
August - 0 - Event8
September - 0 - Event9
October - 23 - Event10
November - 24 - Event11
December - 55 - Event12

my existing query looks like this

SELECT t1.AttendeeID, t1.Score, t1.UserName, Events.EventID, Events.Name AS EventName, DATENAME(MONTH, Events.Starts) AS MonthName, MONTH(Events.Starts) AS MONTHNo
FROM Attendees AS t1 INNER JOIN
Users ON t1.UserName = Users.UserName INNER JOIN
Events ON t1.EventID = Events.EventID
WHERE t1.USERNAME = @UserName AND YEAR(Events.Starts)=@Year

But if a user didn't attend an event that event will not appear on the list.

Thanks in advance for your help

[edited by: Red_Eye at 2:06 pm (utc) on Dec. 18, 2008]

[misja]

You need to use LEFT JOIN instead of INNER JOIN. Inner join only shows records that match in both tables. LEFT JOIN shows everything from the table before and only records that match from the table after the left join. Also you need to remove the t1.username part from the WHERE clause and move it to the join clause.

Your query should be something like this (I haven't tested this):

SELECT t1.AttendeeID, if(t1.Score is not null,t1.Score,0) as Score, t1.UserName, Events.EventID, Events.Name AS EventName, DATENAME(MONTH, Events.Starts) AS MonthName, MONTH(Events.Starts) AS MONTHNo
FROM Events
LEFT JOIN Attendees AS t1 ON t1.EventID = Events.EventID AND t1.USERNAME = @UserName
WHERE YEAR(Events.Starts)=@Year

[Red_Eye]

misja

Thanks for your quick reply. I have now worked it this far
SELECT Events.EventID, Events.Name, Events.Starts, Attendees.Score , MONTH(Events.Starts) AS MonthNo, DATENAME(MONTH, Events.Starts) AS MonthName
FROM Events LEFT JOIN
Attendees ON Attendees.EventID = Events.EventID AND Attendees.UserName='nryan'

WHERE YEAR(Events.starts)=2008
ORDER BY MonthNo

Which is giving me what I want. Thank you

But I can't get the inline if statement to work? Any sugestions I am using sql server 2005

[misja]

I was using MySQL, don't know much about SQL Server.

But you can try using CASE:
CASE Score WHEN NULL THEN '0' ELSE Score END

[Red_Eye]

This is the final query, thanks for all your help

SELECT Events.EventID, Events.Name AS EventName, Events.Starts, CASE WHEN Attendees.Score is not null THEN Attendees.Score ELSE 0 END AS Score, MONTH(Events.Starts) AS MonthNo, DATENAME(MONTH, Events.Starts) AS MonthName
FROM Events LEFT JOIN
Attendees ON Attendees.EventID = Events.EventID AND Attendees.UserName=@UserName

WHERE YEAR(Events.starts)=@Year
ORDER BY Starts