Thanks for your responses but it's not that much help I need with my SQL ;-)
None of the 3 suggestions work. Please look at the examples I gave. I need *all* UserIDs regardless of whether they have a record in History that matches both UserID and WeekID. In other words some UserIDs will only exist for WeekID = 2, other for 1 and 2 and so on. Cheers Dave ----- Original Message ----- From: "Roger Baklund" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Dave" <[EMAIL PROTECTED]> Sent: Thursday, May 23, 2002 6:21 PM Subject: RE: SQL Help Needed > * Dave > > I have 2 tables > > > > Users (UserID) > > > > History (UserID, WeekID, Points) > > > > When a User record is created a record is inserted into History with the > > current WeekID, so for example data could be : > > > > Users > > -------- > > 1111 > > 2222 > > 3333 > > 4444 > > > > History > > ---------- > > 1111 - 1 - 10 > > 2222 - 1 - 20 > > 3333 - 1 - 30 > > 4444 - 2 - 40 > > > > I want to run a query to return one row for each User row and their points > > for any given week. In other words somebody asks for all points in Week 1 > > the result should be : > > > > 1111 - 10 > > 2222 - 20 > > 3333 - 30 > > 4444 - 0 > > > > Or, all points for WeekID 2 would give : > > > > 1111 - 0 > > 2222 - 0 > > 3333 - 0 > > 4444 - 40 > > > > > > I have tried lots of things but I think my brain is just not giving me the > > correct solution. > > I don't know what you have tried or why it did not work for you, but > something like this should work: > > SELECT u.UserID, IF(h.Points,h.Points,0) AS Points > FROM Users AS u > LEFT JOIN History AS h USING(UserID) > WHERE ISNULL(WeekID) OR WeekID = 1 > ORDER BY u.UserID > > -- > Roger > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
