Hi!
You can't use the MAX() function if you're not using a GROUP BY clause.
The MAX() function can only grab the maximum value of a grouped column,
as with MIN(), COUNT(), AVG() etc..
Greetings,
Krister Karlström, Helsinki
Jonathan Crawford wrote:
I think this is what you mean. You just want the timestamp and action from B in addition to something from A (I guessed product_ref), right? The MAX() function should take care of getting the latest timestamp.
explicit join:
SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB
JOIN TableA ON TableA.record_id = TableB.record_id
ORDER BY TableB.action
or if you want to join your tables implicitly in your WHERE clause, similar to what you had before, implicit join:
SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB
WHERE TableA.record_id = TableB.record_id
ORDER BY TableB.action
The problem with the implicit joins versus explicit joins is that you can't ever do OUTER JOINs, where you want many from one table and one (or many) from another table. For example if you want all sales reps and their sales, even if they don't have any. Implicit (or explicit INNER) JOINs will not show you all of the data.
Jonathan Crawford
jcrawf02@xxxxxxxxx
---- Original message ----
Date: 6 Mar 2008 18:46:18 -0000
From: php-db-digest-help@xxxxxxxxxxxxx
Subject: php-db Digest 6 Mar 2008 18:46:18 -0000 Issue 3990
To: php-db@xxxxxxxxxxxxx
php-db Digest 6 Mar 2008 18:46:18 -0000 Issue 3990
Topics (messages 44700 through 44700):
Help with JOIN query
44700 by: Graham Cossey
Administrivia:
To subscribe to the digest, e-mail:
php-db-digest-subscribe@xxxxxxxxxxxxx
To unsubscribe from the digest, e-mail:
php-db-digest-unsubscribe@xxxxxxxxxxxxx
To post to the list, e-mail:
php-db@xxxxxxxxxxxxx
----------------------------------------------------------------------
________________
Date: Thu, 6 Mar 2008 18:46:14 +0000
From: "Graham Cossey" <graham.cossey@xxxxxxxxx>
Subject: Help with JOIN query
To: php-db@xxxxxxxxxxxxx
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
