RES: [PHP] mysql joins

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:[email protected]] 
Enviada em: terça-feira, 25 de março de 2008 09:21
Para: [email protected]
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