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
[EMAIL PROTECTED]



---- Original message ----
Date: 6 Mar 2008 18:46:18 -0000
From: [EMAIL PROTECTED] Subject: php-db Digest 6 Mar 2008 18:46:18 -0000 Issue 3990 To: php-db@lists.php.net


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:
        [EMAIL PROTECTED]

To unsubscribe from the digest, e-mail:
        [EMAIL PROTECTED]

To post to the list, e-mail:
        php-db@lists.php.net


----------------------------------------------------------------------
________________
Date: Thu, 6 Mar 2008 18:46:14 +0000
From: "Graham Cossey" <[EMAIL PROTECTED]> Subject: Help with JOIN query To: php-db@lists.php.net

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

Reply via email to