help with this query please?

Question

help with this query please?

Blair Dee-474691

Right there with Babe

Points: 718
More actions
September 15, 2007 at 2:31 pm

#125665

i am having trouble coming up with the correct sql for this query. i have 3 tables:
users
id
username
groupmembers
id
userid
groupid
groups
id
groupname
weeklyuserscores
userid
groupid
score1
score2
weeknum
what i am trying to get is a list of all users.usernames and that users weeklyuserscore.score1, weeklyuserscore.score2
but only where weeklyuserscore.groupid = 43 and weeklyuserscore.weeknum=2
the problem is that even if a user does not have a record in weeklyuserscores with weenum=2 and groupid=43 i still would like a record returned in the resultset with default values of 0 for score1 and score2
i have made several attempts but not have given me what i am looking for. can someone please help me out?

Viewing 5 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

Please post the query that you've tried, so far... I'm thinking all we need to do is change one of the joins to an outer join and use ISNULL(columnname,0) on a couple of columns.

And didn't you post 4 tables, not 3?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Howell Mr or Mrs. 500 Points: 521 More actions · Answer 2

Sounds like you need to do a left join to the user scores table and use ISNULL to default the missing values to 0. Try this out and see if this works for you.

SELECT

Users.UserNames

,ISNULL(WeeklyUserScore.Score1,0) AS Score1

,ISNULL(WeeklyUserScore.Score2,0) AS Score2

FROM

Users

JOIN GroupMembers

ON Users.ID = Group.UserId

JOIN Groups

ON Groups.ID = GroupMembers.GroupID

LEFT JOIN weeklyuserscores

ON Users.ID = WeeklyUserScores.UserID

AND Groups.ID = WeeklyUserScores.GroupID

WHERE Groups.ID = 43

AND WeeklyUserScores.WeekNum = 2

Blair Dee-474691 Right there with Babe Points: 718 More actions · Answer 3

so it turns out that my database is a mysql 4 database but the principles behind the query should be the same. i tried the query suggested by Grasshopper and it returns 0 records when i say weeklyuserscores.weeknum=2 in the where clause. if i say weeklyuserscores.weeknum=1 then i get results. i assume this is because in myweeklyuserscores table there are no records for weeknum=2 but there are a bunch for weeknum=1. but i need the query to return records regardless of weather or not a record exists for the specified weeknum in weeklyuserscores.

anyway here si the query i am trying now:

SELECT

users.username

,weeklyuserscores.score IS NULL AS Score

,weeklyuserscores.absolutevaluescore IS NULL AS AbsoluteValueScore

FROM users

JOIN groupmembers

ON users.ID = groupmembers.userid

JOIN groups

ON groups.id = groupmembers.groupid

LEFT JOIN weeklyuserscores

ON users.id = weeklyuserscores.userid

AND groups.id = weeklyuserscores.poolid

WHERE groups.id = 43

AND weeklyuserscores.weeknum = 2

Jo Pattyn SSC-Dedicated Points: 32470 More actions · Answer 4

move the "AND weeklyuserscores.weeknum = 2" to the left join clause, otherwise it will be filtered out.

SELECT

users.username

,weeklyuserscores.score IS NULL AS Score

,weeklyuserscores.absolutevaluescore IS NULL AS AbsoluteValueScore

FROM users

JOIN groupmembers

ON users.ID = groupmembers.userid

JOIN groups

ON groups.id = groupmembers.groupid

LEFT JOIN weeklyuserscores

ON users.id = weeklyuserscores.userid

AND groups.id = weeklyuserscores.poolid

AND weeklyuserscores.weeknum = 2

WHERE groups.id = 43