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 JO
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 Tab
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
>
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 th
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
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 P
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) O
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 W
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:[EMAIL PROTECTED]
> Sent: Thu, March 06, 2008 5:17 PM
> To: J. Hill;
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 * FR
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 (h
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:[EMAIL PROTECTED]
> Sent: Thu
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
Is it possible to do a case sensitive query to mySQL? What would an
example syntax look like? Ron
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Hey Ron
Sure can with the BINARY keyword
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
Bastien> From: [EMAIL PROTECTED]> To: php-db@lists.php.net> Date: Thu, 6 Mar
2008 22:45:09 -0500> Subject: [PHP-DB] Case sensitive query> > Is it possible
to do a case sensiti
On Fri, Mar 7, 2008 at 12:27 AM, Jonathan Crawford <[EMAIL PROTECTED]> 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.
>
18 matches
Mail list logo