Re: mysql joins

On Tue, Mar 25, 2008 at 8:20 AM, Steven Macintyre <steven@steven.macintyre.name> wrote: > I have three tables, namely; > > User > - UID > - Firstname > - Surname > - Tel > - Cell > - Email > > Tracker > - UID > - Points > > Winners > - UID > - Datetime (0000-00-00 00:00:00) > > I need to get the following information from the above tables (in my logical sense) > > All users from user with sum(points) as points and datetime > datetime + 14 days > > In English, the all users must be selected, excluding the ones that have won in the last 14 > days and return all the information and the sum of points > > I suspect I would need to use joins here ... but have no clue how to do so ... I have read up > a bit and can work out inner joins from three tables, but not coping with this problem above > > Can someone help me out with this please? > > Many thanks > > Steven See what mileage this gets you. SELECT User.UID, FirstName, Surname, Tel, Cell, Email, SUM(Points) AS TotalPoints FROM User INNER JOIN Tracker ON User.UID = Tracker.UID LEFT OUTER JOIN Winners ON User.UID = Winners.UID WHERE Datetime < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL -14 DAY) OR Datetime IS NULL GROUP BY User.UID, FirstName, Surname, Tel, Cell, Email The OUTER JOIN and the last line (OR Datetime IS NULL) is there so that your query will include results for users who have never won. I don't think it this is optimized (or how you could do so if needed) since the IS NULL condition will probably make the query use a table scan rather than an index. Andrew