[PHP-DB] 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

Re: [PHP-DB] 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

Re: [PHP-DB] Help with JOIN query

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

Re: [PHP-DB] 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

Re: [PHP-DB] 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

Re: [PHP-DB] Help with JOIN query

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

Re: [PHP-DB] Help with JOIN query

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

Re: [PHP-DB] 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)

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, why not something like: SELECT * FROM TableA a, TableB b WHERE

RE: [PHP-DB] 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:[EMAIL PROTECTED] Sent: Thu, March 06, 2008 5:17 PM To: J. Hill;

Re: [PHP-DB] 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

RE: [PHP-DB] 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: [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 confusion. Gary You were getting my hopes up there Gary :-( -- Graham -- PHP Database Mailing List

RE: [PHP-DB] 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:[EMAIL PROTECTED] Sent: Thu,

[PHP-DB] 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

[PHP-DB] Case sensitive query

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

Re: [PHP-DB] Re: Help with JOIN query

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.