Help with JOIN query

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: 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: Help with JOIN query

On Thu, Mar 6, 2008 at 6:54 PM, Krister Karlström
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 =3D 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 =3D 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



--=20
Graham

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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: 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: Help with JOIN query

------=_Part_15177_18699696.1204834270941
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_15177_18699696.1204834270941--

Re: Help with JOIN query

This will give you:

ERROR 1111: 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
> 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: 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 1111: 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
>> 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: Help with JOIN query

On Thu, Mar 6, 2008 at 9:54 PM, 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.
>

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: 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:graham.cossey@gmail.com]
> 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 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: 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 1111: 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
>>> 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: 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: Help with JOIN query

On Thu, Mar 6, 2008 at 10:59 PM, Gary Wardell 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: 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:graham.cossey@gmail.com]
> 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
> 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: Help with JOIN query

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@baker.edu



---- Original message ----
>Date: 6 Mar 2008 18:46:18 -0000
>From: php-db-digest-help@lists.php.net
>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:
> php-db-digest-subscribe@lists.php.net
>
>To unsubscribe from the digest, e-mail:
> php-db-digest-unsubscribe@lists.php.net
>
>To post to the list, e-mail:
> php-db@lists.php.net
>
>
>----------------------------------------------------------- -----------
>________________
>Date: Thu, 6 Mar 2008 18:46:14 +0000
>From: "Graham Cossey"
>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

Re: Re: Help with JOIN query

On Fri, Mar 7, 2008 at 12:27 AM, 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@baker.edu
>

Thank you for your input Jonathan but it's not quite what I need. I
need the latest action from TableB (if it exists) determined by the
record_id matching TableA and where there are more than one matching
record in TableB select the one with the latest timestamp.

As an over-simplified example of what I'm trying to achieve :

TableA
record_id product_ref
1 product1
2 product2

TableB
timestamp record_id action
20080301 1 start
20080302 1 middle
20080301 2 start
20080302 2 middle
20080303 2 end

What I need returned is :

1,product1,middle
2,product2,end

-----------
Graham

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: Help with JOIN query

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@baker.edu
>
>
>
> ---- Original message ----
>> Date: 6 Mar 2008 18:46:18 -0000
>> From: php-db-digest-help@lists.php.net
>> 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:
>> php-db-digest-subscribe@lists.php.net
>>
>> To unsubscribe from the digest, e-mail:
>> php-db-digest-unsubscribe@lists.php.net
>>
>> To post to the list, e-mail:
>> php-db@lists.php.net
>>
>>
>> ------------------------------------------------------------ ----------
>> ________________
>> Date: Thu, 6 Mar 2008 18:46:14 +0000
>> From: "Graham Cossey"
>> 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

Re: Re: Help with JOIN query

Yes, I'm totally with you Graham and I have created your tables and
added some test data (you saw it in a previous post).. But I can't seem
to come up with any solution for you, at least with MySQL 4.0.x.

So if no one else has any brilliant solutions for this I think you'll
need to stick with your solution to make the request in two steps, using
PHP and an array to sort and put everyting together. Now, hopefully, you
don't have hundreds of kilos of rows in your tables.. :)

There might be solutions for this problem with never versions of MySQL,
like using subqueries combined with joins like someone mentioned, but
I'm not sure. I however, have never tested (or even needed to test)
something like that, so I can't help you with that.

If it is not important to keep the history of actions for each product
you could simply update TableB, but I'll guess that's not the case -
otherwise you wouldn't probably been asking us.. :)

Another solution would be to move the previous action to an other table,
like TableC for instance... Then TableC would be your history, TableB
would only have your latest action.. But then again, we would have a
one-to-one relation which basically means that you don't need two tables
anymore. But you could at least consider this, if you are able to alter
the database design a bit.

Greetings,
Krister Karlström, Helsinki

Graham Cossey wrote:

> Thank you for your input Jonathan but it's not quite what I need. I
> need the latest action from TableB (if it exists) determined by the
> record_id matching TableA and where there are more than one matching
> record in TableB select the one with the latest timestamp.
>
> As an over-simplified example of what I'm trying to achieve :
>
> TableA
> record_id product_ref
> 1 product1
> 2 product2
>
> TableB
> timestamp record_id action
> 20080301 1 start
> 20080302 1 middle
> 20080301 2 start
> 20080302 2 middle
> 20080303 2 end
>
> What I need returned is :
>
> 1,product1,middle
> 2,product2,end
>
> -----------
> Graham
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php