Re: Help with JOIN query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux