Prev: display fetched data in excel format
Next: Not updating certain fields in same row
From: "Thiago Pojda" on 25 Mar 2008 09:10
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(a)steven.macintyre.name]
Enviada em: terça-feira, 25 de março de 2008 09:21
Para: php-general(a)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


 | 
Pages: 1
Prev: display fetched data in excel format
Next: Not updating certain fields in same row