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
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
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
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
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
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
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
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)
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
-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
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
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
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
, 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
14 matches
Mail list logo
