Re: [PHP-DB] Help with JOIN query
Hi! Graham Cossey wrote: TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. For instance, to select all columns: select * from TableA join TableB on TableA.record_id = TableB.record_id order by timestamp desc limit 1 So you just join everything, then order by time in descening order and then just returning the first record = the newest record in the database. If you don't want all columns, then simply replace the star with the names of the columns you want. I hope that this is what you wanted the query to do.. :) Cheers, Krister Karlström, Helsinki, Finland -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
On Thu, Mar 6, 2008 at 6:54 PM, Krister Karlström [EMAIL PROTECTED] wrote: Hi! Graham Cossey wrote: TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. For instance, to select all columns: select * from TableA join TableB on TableA.record_id = TableB.record_id order by timestamp desc limit 1 So you just join everything, then order by time in descening order and then just returning the first record = the newest record in the database. If you don't want all columns, then simply replace the star with the names of the columns you want. I hope that this is what you wanted the query to do.. :) I was hoping to avoid joining everything as there can be many entries in TableB for each record in TableA. Also wouldn't your query only return one record? I need to return all records from TableA with the latest action from TableB as well. Graham -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
Hi! Graham Cossey wrote: I was hoping to avoid joining everything as there can be many entries in TableB for each record in TableA. Also wouldn't your query only return one record? I need to return all records from TableA with the latest action from TableB as well. Yes, sorry - I realised that after I sent away my first reply.. :) I have a colleague who always says that people don't READ their mail - the just LOOK at their mail.. It's so true! I missed some of your points. But maybe you got some ideas from my other mail, which I seem to have posted only to you directly.. Oh well, this is a tricky one anyway... /Krister Karlström -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
Hi again! We're getting a bit of topic here, since this is pure SQL.. But anyway... I've played around with this one a bit since it seemed quite interesting... The best I can do is to get the oldest action... select TableA.record_id, product_ref, action, time_stamp from TableA join TableB on TableA.record_id = TableB.record_id group by record_id; Here's the test data: mysql select TableA.record_id, product_ref, action, time_stamp from TableA join TableB on TableA.record_id = TableB.record_id; +---+-+++ | record_id | product_ref | action | time_stamp | +---+-+++ | 1 | 100 | A | 20080306220037 | | 1 | 100 | C | 20080306220041 | | 1 | 100 | E | 20080306220045 | | 2 | 102 | A | 20080306220052 | | 3 | 110 | A | 20080306220055 | | 3 | 110 | E | 20080306220058 | | 4 | 120 | B | 20080306220105 | | 4 | 120 | C | 20080306220109 | +---+-+++ And with the query above we get the opposite of the desired behavior, the oldest action (if that's the order in the database): mysql select TableA.record_id, product_ref, action, time_stamp from TableA join TableB on TableA.record_id = TableB.record_id group by record_id; +---+-+++ | record_id | product_ref | action | time_stamp | +---+-+++ | 1 | 100 | A | 20080306220037 | | 2 | 102 | A | 20080306220052 | | 3 | 110 | A | 20080306220055 | | 4 | 120 | B | 20080306220105 | +---+-+++ 4 rows in set (0.00 sec) Now is the question: Does anyone know how to get the 'group by' clause to leave a specific row 'visible' at top? Like the last inserted or by the order of another column... Since MySQL 4.1 there are also a GROUP_CONCAT() function that can concatenate multiple 'rows' to a string in a desired order, but it does not support the limit statement... so that won't help us much I think. We can get all the actions in a string with the newest first, but then some post-stripping of the data is needed. It seems like you need to do this with two queries in PHP, if no one has an answer to the question stated above. You can always buffer your result in an array in PHP and do whatever sorting you want to before using your data... With the MAX() function we can found out when the last action was made, but we get the wrong action with the correct time: mysql select TableA.record_id, product_ref, action, max(time_stamp) from TableA join TableB on TableA.record_id = TableB.record_id group by record_id; +---+-++-+ | record_id | product_ref | action | max(time_stamp) | +---+-++-+ | 1 | 100 | A | 20080306220045 | | 2 | 102 | A | 20080306220052 | | 3 | 110 | A | 20080306220058 | | 4 | 120 | B | 20080306220109 | +---+-++-+ 4 rows in set (0.00 sec) Hmm... Now I'm stuck! :) Greetings, Krister Karlström, Helsinki, Finland Graham Cossey wrote: I can't see how to accomplish what I need so if anyone has any suggestions they would be gratefully received... I'm using mysql 4.0.20 by the way. I have two tables : TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. At present (using PHP) I do a SELECT on TableA then for each record returned I perform a 2nd SELECT something like : SELECT timestamp, action FROM TableB WHERE record_id = '$record_id' ORDER BY timestamp DESC LIMIT 1 I now want to do it with one query to enable sorting the results by 'action' from TableB. Any suggestions? Hopefully I've made sense, if not I'll happily try and explain further on request. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
You can try adding a quick test to the ON statement... SELECT * FROM TableA INNER JOIN TableB ON TableA.record_id = TableB.record_id AND TableB.timestamp = MAX(TableB.timestamp) Now, I haven't tested it. I can only say the theory of it is accurate. - Jon L. On Thu, Mar 6, 2008 at 12:46 PM, Graham Cossey [EMAIL PROTECTED] wrote: I can't see how to accomplish what I need so if anyone has any suggestions they would be gratefully received... I'm using mysql 4.0.20 by the way. I have two tables : TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. At present (using PHP) I do a SELECT on TableA then for each record returned I perform a 2nd SELECT something like : SELECT timestamp, action FROM TableB WHERE record_id = '$record_id' ORDER BY timestamp DESC LIMIT 1 I now want to do it with one query to enable sorting the results by 'action' from TableB. Any suggestions? Hopefully I've made sense, if not I'll happily try and explain further on request. -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
This will give you: ERROR : Invalid use of group function It seems like the use of an aggregate (or how is it spelled?) function is not allowed in a join statement... /Krister Jon L. wrote: You can try adding a quick test to the ON statement... SELECT * FROM TableA INNER JOIN TableB ON TableA.record_id = TableB.record_id AND TableB.timestamp = MAX(TableB.timestamp) Now, I haven't tested it. I can only say the theory of it is accurate. - Jon L. On Thu, Mar 6, 2008 at 12:46 PM, Graham Cossey [EMAIL PROTECTED] wrote: I can't see how to accomplish what I need so if anyone has any suggestions they would be gratefully received... I'm using mysql 4.0.20 by the way. I have two tables : TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. At present (using PHP) I do a SELECT on TableA then for each record returned I perform a 2nd SELECT something like : SELECT timestamp, action FROM TableB WHERE record_id = '$record_id' ORDER BY timestamp DESC LIMIT 1 I now want to do it with one query to enable sorting the results by 'action' from TableB. Any suggestions? Hopefully I've made sense, if not I'll happily try and explain further on request. -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
I may be a little confused: the desire is to return all the rows from TableA that match the record_id of a row in TableB that has the MAX timestamp? If so, why not something like: SELECT * FROM TableA a, TableB b WHERE a.record_id=b.record_id timestamp=(SELECT MAX(timestamp) FROM TableB) ORDER BY action; I'm guessing I'm confused, that it's something more complicated you're looking for. Jeff Krister Karlström wrote: This will give you: ERROR : Invalid use of group function It seems like the use of an aggregate (or how is it spelled?) function is not allowed in a join statement... /Krister Jon L. wrote: You can try adding a quick test to the ON statement... SELECT * FROM TableA INNER JOIN TableB ON TableA.record_id = TableB.record_id AND TableB.timestamp = MAX(TableB.timestamp) Now, I haven't tested it. I can only say the theory of it is accurate. - Jon L. On Thu, Mar 6, 2008 at 12:46 PM, Graham Cossey [EMAIL PROTECTED] wrote: I can't see how to accomplish what I need so if anyone has any suggestions they would be gratefully received... I'm using mysql 4.0.20 by the way. I have two tables : TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. At present (using PHP) I do a SELECT on TableA then for each record returned I perform a 2nd SELECT something like : SELECT timestamp, action FROM TableB WHERE record_id = '$record_id' ORDER BY timestamp DESC LIMIT 1 I now want to do it with one query to enable sorting the results by 'action' from TableB. Any suggestions? Hopefully I've made sense, if not I'll happily try and explain further on request. -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
On Thu, Mar 6, 2008 at 9:54 PM, J. Hill [EMAIL PROTECTED] wrote: I may be a little confused: the desire is to return all the rows from TableA that match the record_id of a row in TableB that has the MAX timestamp? If so, why not something like: SELECT * FROM TableA a, TableB b WHERE a.record_id=b.record_id timestamp=(SELECT MAX(timestamp) FROM TableB) ORDER BY action; I'm guessing I'm confused, that it's something more complicated you're looking for. Thanks Krister and all for your help thus far. Jeff, I'm after all rows from TableA then the latest action from TableB for each selected record in TableA. I'm starting to think maybe I should build an array of results using 2 queries then sort the array using PHP functionality, but I'd rather do it in MySQL if it's possible. Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Help with JOIN query
Hi, The way I think I'd approach it is to use an outer join where table a is joined to a subquery where the subquery returns only the max timestamp from table b. Gary -Original Message- From: Graham Cossey [mailto:[EMAIL PROTECTED] Sent: Thu, March 06, 2008 5:17 PM To: J. Hill; php-db@lists.php.net Subject: Re: [PHP-DB] Help with JOIN query On Thu, Mar 6, 2008 at 9:54 PM, J. Hill [EMAIL PROTECTED] wrote: I may be a little confused: the desire is to return all the rows from TableA that match the record_id of a row in TableB that has the MAX timestamp? If so, why not something like: SELECT * FROM TableA a, TableB b WHERE a.record_id=b.record_id timestamp=(SELECT MAX(timestamp) FROM TableB) ORDER BY action; I'm guessing I'm confused, that it's something more complicated you're looking for. Thanks Krister and all for your help thus far. Jeff, I'm after all rows from TableA then the latest action from TableB for each selected record in TableA. I'm starting to think maybe I should build an array of results using 2 queries then sort the array using PHP functionality, but I'd rather do it in MySQL if it's possible. Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
Mysql doesn't support subselects in 4.0.x. That was added in 4.1. -Roberto J. Hill wrote: I may be a little confused: the desire is to return all the rows from TableA that match the record_id of a row in TableB that has the MAX timestamp? If so, why not something like: SELECT * FROM TableA a, TableB b WHERE a.record_id=b.record_id timestamp=(SELECT MAX(timestamp) FROM TableB) ORDER BY action; I'm guessing I'm confused, that it's something more complicated you're looking for. Jeff Krister Karlström wrote: This will give you: ERROR : Invalid use of group function It seems like the use of an aggregate (or how is it spelled?) function is not allowed in a join statement... /Krister Jon L. wrote: You can try adding a quick test to the ON statement... SELECT * FROM TableA INNER JOIN TableB ON TableA.record_id = TableB.record_id AND TableB.timestamp = MAX(TableB.timestamp) Now, I haven't tested it. I can only say the theory of it is accurate. - Jon L. On Thu, Mar 6, 2008 at 12:46 PM, Graham Cossey [EMAIL PROTECTED] wrote: I can't see how to accomplish what I need so if anyone has any suggestions they would be gratefully received... I'm using mysql 4.0.20 by the way. I have two tables : TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. At present (using PHP) I do a SELECT on TableA then for each record returned I perform a 2nd SELECT something like : SELECT timestamp, action FROM TableB WHERE record_id = '$record_id' ORDER BY timestamp DESC LIMIT 1 I now want to do it with one query to enable sorting the results by 'action' from TableB. Any suggestions? Hopefully I've made sense, if not I'll happily try and explain further on request. -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Help with JOIN query
Ahh, to bad, I started using it with 5.0. I'm also a long time user of SQL Server. Sorry if I caused confusion. Gary -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help with JOIN query
On Thu, Mar 6, 2008 at 10:59 PM, Gary Wardell [EMAIL PROTECTED] wrote: Ahh, to bad, I started using it with 5.0. I'm also a long time user of SQL Server. Sorry if I caused confusion. Gary You were getting my hopes up there Gary :-( -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Help with JOIN query
I know the feeling. I've been trying to hookup MYSQL as a linked server on MS SQL Server. There are a few articles out there that make mention of it but no where does anybody say exactly how to do it. Gary -Original Message- From: Graham Cossey [mailto:[EMAIL PROTECTED] Sent: Thu, March 06, 2008 6:33 PM To: Gary Wardell; php-db@lists.php.net Subject: Re: [PHP-DB] Help with JOIN query On Thu, Mar 6, 2008 at 10:59 PM, Gary Wardell [EMAIL PROTECTED] wrote: Ahh, to bad, I started using it with 5.0. I'm also a long time user of SQL Server. Sorry if I caused confusion. Gary You were getting my hopes up there Gary :-( -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
