Help join this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help join this query

Author

Topic

adrox
Starting Member

17 Posts

Posted - 2005-05-13 : 15:17:14

I have a this query:

select msp.memberID, r.Week,
(case when msp.PointsAwarded > 0 AND rd.Finish < 11 AND rd.Finish > 0 then 1 ELSE 0 END) PointsAwarded
FROM MemberStreakPick msp
INNER JOIN raceDriver rd ON rd.RaceDriverID = msp.RaceDriverID
LEFT OUTER JOIN Race r ON r.raceID = rd.raceID
where memberID = 719181
order by r.week

which returns the following results:

MEMBERID WEEK PointsAwarded
719181 1 1
719181 2 1
719181 3 1
719181 4 1
719181 6 1
719181 7 1
719181 8 1
719181 9 1
719181 10 1
719181 11 0

My problem is it is skipping week 5 because there is not a related record for week 5 in the memberStreakPick table. So what I would like to do is join it in a way that it would return these results

MEMBERID WEEK PointsAwarded
719181 1 1
719181 2 1
719181 3 1
719181 4 1
719181 5 0
719181 6 1
719181 7 1
719181 8 1
719181 9 1
719181 10 1
719181 11 0

Thanks for any help.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-05-15 : 20:48:44

How about this:

select msp.memberID, r.Week,
(case when msp.PointsAwarded > 0 AND rd.Finish < 11 AND rd.Finish > 0 then 1 ELSE 0 END) PointsAwarded
FROM
Race r
LEFT OUTER JOIN
raceDriver rd ON r.raceid=rd.RaceDriverID
INNER JOIN
MemberStreakPick msp
on rd,RaceDriverID = msp.RaceDriverID
where memberID = 719181 
order by r.week

I'm assuming that Race hase EVERY week in it...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

adrox
Starting Member

17 Posts

Posted - 2005-05-16 : 08:48:29

Your assumption is correct, race does have every week in it. This query of yours returns the same results as mine. The user does not have a record for week 5 in the other two tables (raceDriver and memberStreakPick) so I need to put a place holder in the query results with memberID, week, and ptsAwarded.

If the user didnt have a pick like in this case the result set should put this as a place holder:

719181, 5, 0

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-05-16 : 08:55:08

Look into using a "TALLY table" to control joining to missing data....(left join)
Search here for instances/examples.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-05-16 : 20:42:22

If the race table has every week in it, we should get every week if get the join right. What happens if you change the INNER JOIN to a LEFT OUTER JOIN? I think the query I posted is what you want, but since you say it is returning the same thing, I must be missing something. Post your table info and some sample data. We'll get this...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

adrox
Starting Member

17 Posts

Posted - 2005-05-17 : 09:11:48

This query:

select msp.memberID, r.Week,
(case when msp.PointsAwarded > 0 AND rd.Finish < 11 AND rd.Finish > 0 then 1 ELSE 0 END) PointsAwarded
FROM MemberStreakPick msp
INNER JOIN raceDriver rd ON rd.RaceDriverID = msp.RaceDriverID
LEFT OUTER JOIN Race r ON r.raceID = rd.raceID
where memberID = 719181
order by r.week

Returns this:

MEMBERID WEEK PointsAwarded
719181 1 1
719181 2 1
719181 3 1
719181 4 1
719181 6 1
719181 7 1
719181 8 1
719181 9 1
719181 10 1
719181 11 0

and this

select msp.memberID, r.Week,
(case when msp.PointsAwarded > 0 AND rd.Finish < 11 AND rd.Finish > 0 then 1 ELSE 0 END) PointsAwarded
FROM
Race r
LEFT OUTER JOIN
raceDriver rd ON r.RaceID=rd.RaceID
INNER JOIN
MemberStreakPick msp
on rd.RaceDriverID = msp.RaceDriverID
where memberID = 719181
order by r.week

also returns this:

MEMBERID WEEK PointsAwarded
719181 1 1
719181 2 1
719181 3 1
719181 4 1
719181 6 1
719181 7 1
719181 8 1
719181 9 1
719181 10 1
719181 11 0

Skiping week five.

Table info:

Race:
RaceID, Week, Name, WhenStart

(There is a record here for every week and race of the season, 36 weeks)

RaceDriver:
RaceDriverID, DriverID, RaceID, Finish

(each week a new raceDriverID is created based on DriverID's that are in the race)

MemberStreakPick:
MemberID, RaceDriverID, PointsAwarded(flag 1 yes, 0 No)

(If the user makes a pick for this race it is stored here)

MemberStreak
MemberID, totalPoints

(main user info table)

adrox
Starting Member

17 Posts

Posted - 2005-05-17 : 09:19:23

Sorry should of added this:

Race:
RaceID, Week, Name, WhenStart
1001, 1, Subway 500, 2/22/2005 06:00:00 PM
1002, 2, ..., ...
1003, 3, .., ...
1004, 4, ..., ...
1005, 5, .., ...
1006, 6,.., ...

(There is a record here for every week and race of the season, 36 weeks)

RaceDriver:
RaceDriverID, DriverID, RaceID, Finish
(this is one driver for the 6 weeks)
104525, 1020, 1001, 11
104324, 1020, 1002, 1
106565, 1020, 1003, 15
109792, 1020, 1004, 11
111241, 1020, 1006, 9
113242, 1020, 1006, 40

(each week a new raceDriverID is created based on DriverID's that are in the race)

MemberStreakPick:
MemberID, RaceDriverID, PointsAwarded(flag 1 yes, 0 No)
(user picks driver above for 5 of the 6 weeks)
719181,104525, 1
719181,104324, 1
719181,106565, 1
719181,109792, 1
719181,113242, 1

(If the user makes a pick for this race it is stored here, in this case the user has no record for week 5)

MemberStreak
MemberID, totalPoints
719181, 650
(main user info table)

adrox
Starting Member

17 Posts

Posted - 2005-05-17 : 12:11:50

And using left joins on both produce the same result

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-05-18 : 07:39:13

Ok Adrox,
I'll look at it tonight. Gotta go to the doc today for some tests...

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

adrox
Starting Member

17 Posts

Posted - 2005-05-19 : 10:48:23

Still having trouble with this. Anyones imput would be helpful.

Thanks

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-05-19 : 11:38:32

Hey Adrox,
This work for you?

select msp.memberID, r.Week,
(case when msp.PointsAwarded > 0 AND rd.Finish < 11 AND rd.Finish > 0 then 1 ELSE 0 END) PointsAwarded
FROM  race r
left outer JOIN raceDriver rd ON rd.RaceID = r.raceid
left outer JOIN MemberStreakPick msp ON rd.racedriverID = msp.racedriverid
and msp.memberid='719181'
order by r.week

Andy

BTW: when you post table info, if you do it as code it makes it easier for everyone to help you...

There's never enough time to type code right,
but always enough time for a hotfix...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-19 : 12:56:22

Let's break it down, "Dr. Cross Join"-style:

1) Don't worry about the final output and blank rows yet. make sure your calculations are good to go. Keep in mind you want to return 1 row per combination of MemberID/RaceID. Don't worry about filtering for a particular memberID just yet, worry about writing the SELECT with a GROUP BY on MemberID/RaceID that returns the results you want. Let's refer to this select as "MemberRacePoints":


Select 
  M.MemberID, RD.RaceID, M.PointsAwarded
from 
  MemberStreakPick M
inner join 
  RaceDriver RD
on 
  RD.RaceDriverID = M.RaceDriverID
where
  RD.Finish between 1 and 10 and
  M.PointsAwarded = 1

2) Now, put that first SELECT aside and now think about your final results. You need to consider the population of all members and all races, and return an indication if that combination resulted in points awarded. To consider all members, all races, a cross join is used:


select 
  M.MemberID, R.RaceID, R.Week
from 
  Members M
cross join
  Races R

It's pretty simple what it is doing. Don't worry about calculations in this step, worry about returning the rows you need to produce the results you are looking for. Also don't worry about a particular MemberID just yet.

At this point you have two very simple queries that are easy to troubleshoot, there's no worries about nulls and left outer joins or formatting, you have just broken it down into small, simple steps.

3) Now put it all together -- all you need to do take your previous SELECT, which returns all combos you need to consider, and do a LEFT OUTER JOIN to "MemberRacePoints", and filter for the MemberID you want:


Select
  M.MemberID, R.RaceID, R.Week,  ISNULL(MRP.PointsAwarded,0) as PointsAwarded
from
  Members M
cross join
  Races R
left outer join
 (... the SQL for MemberRacePoints goes here ...) MRP
on
  R.RaceID = MRP.RAceID and 
  M.MemberID = MRP.MemberID
where
  M.MemberID = @MemberID

As always, remember never to include columns from outer tables in your WHERE clause, since that turns the outer join into an inner join.

- Jeff

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-05-19 : 15:16:48

Hey Jeff!