On Tue, Mar 25, 2008 at 8:20 AM, Steven Macintyre
<steven@xxxxxxxxxxxxxxxxxxxxx> 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
