homepage Welcome to WebmasterWorld Guest from 54.242.140.11
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Help with simple query
Well complex for me
Red_Eye

10+ Year Member



 
Msg#: 3810393 posted 2:04 pm on Dec 18, 2008 (gmt 0)

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

10+ Year Member



 
Msg#: 3810393 posted 2:43 pm on Dec 18, 2008 (gmt 0)

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

10+ Year Member



 
Msg#: 3810393 posted 3:10 pm on Dec 18, 2008 (gmt 0)

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

10+ Year Member



 
Msg#: 3810393 posted 3:24 pm on Dec 18, 2008 (gmt 0)


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

10+ Year Member



 
Msg#: 3810393 posted 3:32 pm on Dec 18, 2008 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved