Re: [PHP-DB] 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 [EMAIL PROTECTED] Original message Date: 6 Mar 2008 18:46:18 - From: [EMAIL PROTECTED] Subject: php-db Digest 6 Mar 2008 18:46:18 - Issue 3990 To: php-db@lists.php.net php-db Digest 6 Mar 2008 18:46:18 - 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 + 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
Re: [PHP-DB] 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 1start 20080302 1middle 20080301 2start 20080302 2middle 20080303 2end 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
[PHP-DB] help cursors odbc_connect odbc_fetch_row
I am using the odbc support in PHP 5.2 to connect to a sqlserver 2005
database. The odbc driver being used is the SQLSRV32.dll provided
as part of the install.
In the code below I find that if I use SQL_CUR_USE_ODBC as an option
to odbc_connect, the odbc_fetch_row() call below fails even though the
odbc_exec was successful.
However, if I remove the SQL_CUR_USE_ODBC option the code executes
successfully.
I have another query that uses some left outer joins on two tables and
am only able to read the results with odbc_fetch_row only if
SQL_CUR_USE_ODBC is used in odbc_conect.
How does one decide which cursor option to use?
Thanks
Abhay
$connect = odbc_connect(DB, xx, , SQL_CUR_USE_ODBC);
if (!$connect) {
print(Connect failed.);
exit();
}
$query=SELECT TOP 1 Version FROM Versions ORDER BY VersionIndex
DESC;
$result = odbc_exec($connect, $query);
if (!$result) {
print( Failed to execute $query.);
exit();
}
//odbc_fetch_row below is successful if no cursor or SQL_CURSOR_FORWARD_ONLY
//is specified in the odbc_connect call. Why does
//SQL_CUR_USE_ODBC fail?
if (odbc_fetch_row($result)) {
$data = odbc_result($result, 1);
print(GOT data $data.);
}
var callCount = 0; function rmvScroll( msg ) { if ( ++callCount
10 ) { msg.style.visibility = visible; }if ( callCount msg.clientHeight
) { newHeight = msg.scrollHeight + delta; } delta = msg.offsetWidth -
msg.clientWidth; delta = ( isNaN( delta )? 1 : delta + 1 ); if (
msg.scrollWidth msg.clientWidth ) { newWidth = msg.scrollWidth + delta; }
msg.style.overflow = visible; msg.style.visibility = visible;if (
newWidth 0 || newHeight 0 ) { var ssxyzzy = document.getElementById(
ssxyzzy ); var cssAttribs = ['#' + msg.id + '{']; if ( newWidth 0 )
cssAttribs.push( 'width:' + newWidth + 'px;' ); if ( newHeight 0 )
cssAttribs.push( ' height:' + newHeight + 'px;' ); cssAttribs.push( '}' );
try {ssxyzzy.sheet.deleteRule( 0 );ssxyzzy.sheet.insertRule(
cssAttribs.join(), 0 ); } catch( e ){} } } function imgsDone( msg ) // for
Firefox, we need to scan for images that haven't set their width yet { var
imgList =
msg.getElementsByTagName( IMG ); var len = ((imgList == null)? 0 :
imgList.length); for ( var i = 0; i [input] [input] [input] [input]
[input] [input] [input] [input]
DeleteReply
-
Never miss a thing. Make Yahoo your homepage.
