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@xxxxxxxxxxxxxxxxxxxxx]
Enviada em: terça-feira, 25 de março de 2008 09:21
Para: php-general@xxxxxxxxxxxxx
Assunto: 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
