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] 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 [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
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. 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] 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] Re: Help with UPDATE query
In article [EMAIL PROTECTED], [EMAIL PROTECTED] says... Hi, I have two columns in my Bookings table of type DATETIME - Booking_Start_Date and Boking_End_Date. How can i update every row so that all of the times for Booking_Start_Date are 09.00 and all of the times for Booking_End_Date are 17.30, without affecting any of the dates? Thanks for your help Off the top of my head - use mysql's DATE_FORMAT to build a date string, then concatenate the required time string and use that as the update value. Something like UPDATE table SET Booking_Start_date = CONCAT(DATE_FORMAT(Booking_Start_Date, your format here),'09.00'), Booking_End_date = CONCAT(DATE_FORMAT(Booking_End_Date, your format here),'17.30'); You'll need to play a bit with that - and of course test on a backup. Cheers -- Quod subigo farinam A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing on usenet? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Help with a query
i am not testing this... just writing it here. and it is 2am ! SELECT shopping_cart.item_id, shopping_cart.subtotal, shopping_cart.quantity FROM shopping_cart, orders WHERE shopping_cart.order_id = orders.order_id AND orders.session_id = session_id() AND orders.customer_id = '$customer_id' i guess u could give it a try. just a simple 2 table join. i may not have the fields and order in place but try it Joel Colombo Jonathan Villa [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I can't figure this query out. I want to pull data from 2 tables but using a primary key from one. Here is what I think it should look like. SELECT item_id, subtotal, quantity from shopping_cart WHERE order_id = (SELECT order_id FROM orders WHERE session_id = session_id()) AND customer_id = $customer_id; -Jonathan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
