Multiple Left Joins in SQL

[mongoose00318]

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.

[Barand]

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

[mongoose00318]

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

[Barand]

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.

[mongoose00318]

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
        )

)

 

[kicken]

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