mongoose00318 Posted June 1, 2020 Share Posted June 1, 2020 I'm trying to perform two joins in my SQL statement but it's giving strange results back. Here is my SQL: SELECT msgs.chat_message, msgs.order_id, msgs.timestamp, msgsTo.id, msgsTo.message_id, msgsTo.from_user_id, msgsTo.to_user_id, msgsTo.status, orders.job_number, orders.enterprise FROM chat_message AS msgs LEFT JOIN chat_to_users AS msgsTo ON msgs.chat_message_id = msgsTo.id LEFT JOIN production_data AS orders ON msgs.order_id = orders.id WHERE msgsTo.to_user_id = 7 ORDER BY msgs.timestamp DESC And here is the results I get back from that: But here's the strange part...there are two records with an order id of 666 where something is off; either the to_user_id is wrong or the order_id itself is wrong. Quote Link to comment https://forums.phpfreaks.com/topic/310880-multiple-left-joins-in-sql/ Share on other sites More sharing options...
Barand Posted June 1, 2020 Share Posted June 1, 2020 I don't know if it's the only problem (can't see your data) but the WHERE condition should be part of the JOIN ON condition for "chat_to_users" . . . FROM chat_message AS msgs LEFT JOIN chat_to_users AS msgsTo ON msgs.chat_message_id = msgsTo.id AND msgsTo.to_user_id = 7 LEFT JOIN production_data AS orders ON msgs.order_id = orders.id ORDER BY msgs.timestamp DESC otherwise the join behaves as an INNER JOIN Quote Link to comment https://forums.phpfreaks.com/topic/310880-multiple-left-joins-in-sql/#findComment-1578589 Share on other sites More sharing options...
mongoose00318 Posted June 2, 2020 Author Share Posted June 2, 2020 (edited) Hmm..it still is behaving strangely. It's like it's just replacing data (ie like the order id)...here is an example after I updated the statement to this: SELECT msgs.chat_message, msgs.order_id, msgs.timestamp, msgsTo.id, msgsTo.from_user_id, msgsTo.to_user_id, msgsTo.status, orders.job_number, orders.enterprise FROM chat_message AS msgs LEFT JOIN chat_to_users AS msgsTo ON msgs.chat_message_id = msgsTo.id AND msgsTo.to_user_id = 7 LEFT JOIN production_data AS orders ON msgs.order_id = orders.id ORDER BY msgs.timestamp DESC Which returns this data: https://imgur.com/4ux5Lu6 I have highlighted a specific row (with an id of 55) and then pulled up that row https://imgur.com/39Tougx See how on the first screenshot it shows an order id of 666 when in fact the actual record has an id of 759? You said you can't see my data...maybe table structure would help? CREATE TABLE `chat_message` ( `chat_message_id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) DEFAULT NULL, `chat_message` text NOT NULL, `timestamp` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time message was sent', PRIMARY KEY (`chat_message_id`) ) ENGINE=InnoDB AUTO_INCREMENT=169 DEFAULT CHARSET=latin1 CREATE TABLE `chat_to_users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `message_id` int(11) NOT NULL, `order_id` int(11) DEFAULT NULL, `to_user_id` int(11) NOT NULL DEFAULT 0, `from_user_id` int(11) NOT NULL, `read_timestamp` timestamp NULL DEFAULT NULL COMMENT 'time user read message', `status` int(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=230 DEFAULT CHARSET=latin1 CREATE TABLE `production_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `job_number` int(8) NOT NULL, `enterprise` tinytext NOT NULL, `part_number` text NOT NULL, `description` text NOT NULL, `qty` int(11) NOT NULL, `line_item` varchar(11) NOT NULL, `as400_ship_date` date DEFAULT NULL, `hold_reason` text NOT NULL DEFAULT '0', `hold_date` date DEFAULT NULL, `insert_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'time order was inserted', PRIMARY KEY (`id`), KEY `job_line` (`job_number`,`line_item`) ) ENGINE=MyISAM AUTO_INCREMENT=1685 DEFAULT CHARSET=latin1 FYI: I just realized this post was moved the MySQL help. I should have posted it there. Sorry about that. Edited June 2, 2020 by mongoose00318 Quote Link to comment https://forums.phpfreaks.com/topic/310880-multiple-left-joins-in-sql/#findComment-1578599 Share on other sites More sharing options...
Barand Posted June 2, 2020 Share Posted June 2, 2020 Still don't know what chat message data or production data you have. All I see are four chat_message_to records. Nor do I know what results you are expecting. Quote Link to comment https://forums.phpfreaks.com/topic/310880-multiple-left-joins-in-sql/#findComment-1578600 Share on other sites More sharing options...
mongoose00318 Posted June 2, 2020 Author Share Posted June 2, 2020 Okay...I've got it doing what I need it doing...lol but I know I'm doing some repetitive stuff but I couldn't figure out what I was doing wrong with my query above. Here is my current function: //get all messages directd to a specifc user function get_messages_directed_to( $user_id, $pdo ) { //get all entries from chat_to_users $query = " SELECT msgsTo.*, msgs.timestamp FROM chat_to_users AS msgsTo LEFT JOIN chat_message AS msgs ON msgsTo.message_id = msgs.chat_message_id WHERE to_user_id = :user_id ORDER BY msgs.timestamp DESC "; $statement = $pdo->prepare( $query ); $statement->execute( [ 'user_id' => $user_id ] ); $result = $statement->fetchAll(); foreach ( $result as $r ) { $query = " SELECT msgs.*, orders.job_number, orders.enterprise FROM chat_message AS msgs LEFT JOIN production_data AS orders ON ". $r['order_id'] ." = orders.id WHERE msgs.chat_message_id = ". $r['message_id'] ." "; $statement = $pdo->prepare( $query ); $statement->execute(); $results = $statement->fetchAll(); $structure_results[] = [ 'from_user_id' => $r['from_user_id'], 'to_user_id' => $r['to_user_id'], 'order_id' => $r['order_id'], 'order_job_number' => $results[0]['job_number'], 'order_enterprise' => $results[0]['enterprise'], 'chat_message_id' => $results[0]['chat_message_id'], 'chat_message' => $results[0]['chat_message'], 'chat_timestamp' => $results[0]['timestamp'], ]; } print_r( $structure_results ); } Here is the resulting array with a user_id of 7: Array ( [0] => Array ( [from_user_id] => 23 [to_user_id] => 7 [order_id] => 366 [order_job_number] => 22004702 [order_enterprise] => CONOCO [chat_message_id] => 152 [chat_message] => Have to repaint the cabinet. Wrong color on the BOM. [chat_timestamp] => 2020-05-29 09:39:30 ) [1] => Array ( [from_user_id] => 18 [to_user_id] => 7 [order_id] => 157 [order_job_number] => 22992201 [order_enterprise] => CONOCO [chat_message_id] => 138 [chat_message] => LEDS came in on the 5-22 please advise new ship date [chat_timestamp] => 2020-05-28 15:06:38 ) [2] => Array ( [from_user_id] => 18 [to_user_id] => 7 [order_id] => 215 [order_job_number] => 22991891 [order_enterprise] => CONOCO [chat_message_id] => 137 [chat_message] => According to Amanda principals to be rcvd 5-28 can you please advise new ship date thank you [chat_timestamp] => 2020-05-28 14:15:40 ) [3] => Array ( [from_user_id] => 6 [to_user_id] => 7 [order_id] => 1051 [order_job_number] => 22994751 [order_enterprise] => SHRVIE [chat_message_id] => 134 [chat_message] => We are needing a traveler for the 1x price faces part#FA2026SG.1LD [chat_timestamp] => 2020-05-28 11:11:16 ) [4] => Array ( [from_user_id] => 6 [to_user_id] => 7 [order_id] => 759 [order_job_number] => 22972681 [order_enterprise] => GREASEMN [chat_message_id] => 109 [chat_message] => panels are finished [chat_timestamp] => 2020-05-27 07:48:37 ) [5] => Array ( [from_user_id] => 8 [to_user_id] => 7 [order_id] => 3 [order_job_number] => 22982151 [order_enterprise] => AMERICAN [chat_message_id] => 95 [chat_message] => Derrick needs the can for this 3'x6' American Standard [chat_timestamp] => 2020-05-26 11:21:49 ) [6] => Array ( [from_user_id] => 6 [to_user_id] => 7 [order_id] => 634 [order_job_number] => 22000782 [order_enterprise] => EXXON [chat_message_id] => 91 [chat_message] => Waiting on vinyl to arrive, is supposed to be here this week. [chat_timestamp] => 2020-05-26 05:55:25 ) [7] => Array ( [from_user_id] => 6 [to_user_id] => 7 [order_id] => 759 [order_job_number] => 22972681 [order_enterprise] => GREASEMN [chat_message_id] => 89 [chat_message] => had to be repainted, will have ready by 5/28. [chat_timestamp] => 2020-05-26 05:07:44 ) [8] => Array ( [from_user_id] => 6 [to_user_id] => 7 [order_id] => 758 [order_job_number] => 22972681 [order_enterprise] => GREASEMN [chat_message_id] => 88 [chat_message] => had to be repainted, will have ready by 5/28 [chat_timestamp] => 2020-05-26 05:06:37 ) [9] => Array ( [from_user_id] => 6 [to_user_id] => 7 [order_id] => 757 [order_job_number] => 22972681 [order_enterprise] => GREASEMN [chat_message_id] => 87 [chat_message] => had to be repainted, will have ready by 5/28 [chat_timestamp] => 2020-05-26 05:05:34 ) [10] => Array ( [from_user_id] => 6 [to_user_id] => 7 [order_id] => 1026 [order_job_number] => 22973261 [order_enterprise] => SHRVIE [chat_message_id] => 86 [chat_message] => One of the prints was a Shop N Stop. took pictures sent to purchasing. requested a new one. [chat_timestamp] => 2020-05-25 22:33:44 ) [11] => Array ( [from_user_id] => 6 [to_user_id] => 7 [order_id] => 1048 [order_job_number] => 22986511 [order_enterprise] => SHRVIE [chat_message_id] => 85 [chat_message] => Waiting on the vinyl to come in. They are supposed to be in this week. [chat_timestamp] => 2020-05-25 21:12:38 ) [12] => Array ( [from_user_id] => 6 [to_user_id] => 7 [order_id] => 9 [order_job_number] => 35550690 [order_enterprise] => AMOCO [chat_message_id] => 81 [chat_message] => waiting on vinyl to arrive [chat_timestamp] => 2020-05-21 10:01:49 ) [13] => Array ( [from_user_id] => 4 [to_user_id] => 7 [order_id] => 75338 [order_job_number] => [order_enterprise] => [chat_message_id] => 44 [chat_message] => Drawing is updated [chat_timestamp] => 2020-04-21 10:28:07 ) ) Quote Link to comment https://forums.phpfreaks.com/topic/310880-multiple-left-joins-in-sql/#findComment-1578602 Share on other sites More sharing options...
kicken Posted June 2, 2020 Share Posted June 2, 2020 You have what appear to be a number of problems with your original query. FROM chat_message AS msgs LEFT JOIN chat_to_users AS msgsTo ON msgs.chat_message_id = msgsTo.id AND msgsTo.to_user_id = 7 Here you are joining based on the condition chat_messages.chat_message_id = chat_to_users.id. Both of those columns are AUTO_INCREMENT PRIMARY KEYS according to your table definitions which means that condition makes no sense. Only one side of a condition should be to a AUTO_INCREMENT column, the other side needs to reference a regular INT column that's a reference. Based on your table definitions, you probably want to be making the condition on the chat_to_users.message_id column FROM chat_message AS msgs LEFT JOIN chat_to_users AS msgsTo ON msgs.chat_message_id = msgsTo.message_id AND msgsTo.to_user_id = 7 You also seem to have an order_id column that is linking the tables. Whether this is necessary or not is unclear, but if you're expecting them to be the same then that should also be part of your join condition. FROM chat_message AS msgs LEFT JOIN chat_to_users AS msgsTo ON msgs.chat_message_id = msgsTo.message_id AND msgs.order_id = msgsTo.order_id AND msgsTo.to_user_id = 7 Quote Link to comment https://forums.phpfreaks.com/topic/310880-multiple-left-joins-in-sql/#findComment-1578605 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.