mysql joins

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 + 14days

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

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

not sure how timestamps work in MySQL, but I've written this in Oracle:

CREATE TABLE USaR (
UsID char(255) null,
Firstname char(255) NULL,
Surname char(255) NULL,
Tel char(255) NULL,
Cell char(255) NULL,
Email char(255) NULL
)
/
CREATE TABLE Tracker(
UsID CHAR(255) NULL,
Points CHAR(255) NULL
)
/
CREATE TABLE Winners(
UsiD CHAR(255) NULL,
DateTime DATE NULL
)
/

/* Inserted some values in those tables and then executed: */

select
us.usid, --I couldn't get the Firstname as it's not a group by element (?)
Sum(tr.points)
from
usar us, --in mysql you'll have to do 'usar as us'
tracker tr, --tracker as tr
winners wn --winners as wn
where
us.usid = tr.usid --here is the join magic
and us.usid = wn.usid --and here
AND wn.datetime < (SYSDATE - 14) --winner date has to be less than 14 days
from today
GROUP BY us.usid --separate per user;


I hope this helps :)

This will *NOT* bring you users that never won or have no points (since they
don't have any record in winners table)

Regards,
Thiago

-----Mensagem original-----
De: Steven Macintyre [mailto:steven@steven.macintyre.name]
Enviada em: terça-feira, 25 de março de 2008 09:21
Para: php-general@lists.php.net
Assunto: [php] mysql joins

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



--
PHP General Mailing List (http://www.php.net/) To unsubscribe,
visit: http://www.php.net/unsub.php