[PHP] mysql joins
I have three tables, namely; User - UID - Firstname - Surname - Tel - Cell - Email Tracker - UID - Points Winners - UID - Datetime (-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
Re: [PHP] mysql joins
On Tue, Mar 25, 2008 at 8:20 AM, Steven Macintyre [EMAIL PROTECTED] wrote: I have three tables, namely; User - UID - Firstname - Surname - Tel - Cell - Email Tracker - UID - Points Winners - UID - Datetime (-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
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: 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 (-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
Re: [PHP] mysql joins
Steven Macintyre [EMAIL PROTECTED] wrote: I have three tables, namely; User - UID - Firstname - Surname - Tel - Cell - Email Tracker - UID - Points Winners - UID - Datetime (-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 What PHP code have you written so far? I personally would do a query on one table then use the results to grab the information from the other table. Some use joins, but I haven't seen a ton of time loss between the multi-short calls versus a join.. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL joins
why do i get errors on MySQL queries that have joins? alyx - Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games
RE: [PHP] MySQL joins
can you supply some examples please -Original Message- From: Alexander P. Javier [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 26, 2002 10:34 AM To: [EMAIL PROTECTED] Subject: [PHP] MySQL joins why do i get errors on MySQL queries that have joins? alyx - Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games
[PHP] MySQL joins
the query is: select appointment.*, date_format(appointment.startdatetime,'%m-%d-%Y') as yrappt from appointment left join guest on appointment.appointment_id = guest.appointment_id i think it's syntactically correct, it returns the correct rows when run with the mysql.exe program but gets an error when run thru my php code in a browser. by the way, i use apache-php-mysql for windows. and i program locally. please H =( alyx - Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games
Re: [PHP] MySQL joins
Try this instead: SELECT *, DATE_FORMAT(appointment.startdatetime, '%m-%d-%y') AS yrappt FROM appointment LEFT JOIN guest USING (appointment_id) That should work, but I don¹t know what your table is like so I can only guess all the required elements are there. Rick Every person you meet - and everything you do in life - is an opportunity to learn something. - Tom Clancy From: Alexander P. Javier [EMAIL PROTECTED] Date: Mon, 25 Feb 2002 15:40:47 -0800 (PST) To: [EMAIL PROTECTED] Subject: [PHP] MySQL joins the query is: select appointment.*, date_format(appointment.startdatetime,'%m-%d-%Y') as yrappt from appointment left join guest on appointment.appointment_id = guest.appointment_id i think it's syntactically correct, it returns the correct rows when run with the mysql.exe program but gets an error when run thru my php code in a browser. by the way, i use apache-php-mysql for windows. and i program locally. please H =( alyx - Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
