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
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
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
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
