* 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
