我有這個表：

default_messages

CREATE TABLE IF NOT EXISTS `default_messages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`message` text COLLATE utf8_unicode_ci NOT NULL,
`sender_user_id` int(11) NOT NULL,
`reply_to_message_id` int(11) NOT NULL,
`thread_root_message_id` int(11) NOT NULL,
`date` datetime NOT NULL,
`deleted` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

預設配置文件：

CREATE TABLE IF NOT EXISTS `default_profiles` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
`created_by` int(11) DEFAULT NULL,
`ordering_count` int(11) DEFAULT NULL,
`user_id` int(11) unsigned NOT NULL,
`display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`company` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`bio` text COLLATE utf8_unicode_ci,
`dob` int(11) DEFAULT NULL,
`gender` set('m','f','') COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`mobile` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`address_line1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`address_line2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`address_line3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`postcode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`updated_on` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

和

預設收件人：

CREATE TABLE IF NOT EXISTS `default_recipient` (
`message_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`read` int(11) NOT NULL DEFAULT '0',
`recipient_read_date` datetime DEFAULT NULL,
`deleted` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

然後我有這個查詢來獲取一些消息：

SELECT DISTINCT p.display_name, p.first_name, p.last_name, rcp.*, msg.* FROM default_messages msg LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id) LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id) WHERE rcp.user_id = 1 AND rcp.deleted = 0 ORDER BY msg.date DESC

我需要從 default_profile 獲取值，其中 default_profile.user_id = default_messages.sender_user_id 但由於某種原因我得到了錯誤的值。我的查詢有問題嗎？

以下是一些用於測試目的的值以及用於創建表的完整腳本：http: //pastebin.com/T3mXfi0k

首先，您返回的數據有什麼“錯誤”？

我的猜測是，您打算使用[INNER] JOIN僅返回有匹配記錄的行的 a，而不是LEFT JOIN無論右表中是否存在匹配項都將返回左表中所有行的 a。

例如：

mysql> select distinct p.display_name, p.first_name, p.last_name, m.sender_user_id 
   -> FROM default_profiles p 
   -> JOIN default_messages m ON m.sender_user_id = p.user_id;
+--------------------+------------+------------+----------------+
| display_name       | first_name | last_name  | sender_user_id |
+--------------------+------------+------------+----------------+
| Reynier Perez Mira | Reynier    | Perez Mira |              1 |
| User1              | User1      | Lastname1  |              2 |
+--------------------+------------+------------+----------------+
2 rows in set (0.00 sec)

對比

mysql> select distinct p.display_name, p.first_name, p.last_name, m.sender_user_id 
   -> FROM default_profiles p
   -> LEFT JOIN default_messages m ON m.sender_user_id = p.user_id;
+--------------------+------------+---------------+----------------+
| display_name       | first_name | last_name     | sender_user_id |
+--------------------+------------+---------------+----------------+
| Reynier Perez Mira | Reynier    | Perez Mira    |              1 |
| User1              | User1      | Lastname1     |              2 |
| Tomas Losis        | Tomas      | Losis         |           NULL |
| Roberto            | Roberto    | Scharffenorth |           NULL |
| webmaster 3wd      | webmaster  | 3wd           |           NULL |
| polar              | polar      | pilsen        |           NULL |
| rubi               | rubi       | acosta        |           NULL |
+--------------------+------------+---------------+----------------+
7 rows in set (0.00 sec)

我會像這樣重寫您提供的查詢：

SELECT DISTINCT p.display_name, p.first_name, p.last_name, rcp.*, msg.*
FROM default_profiles p 
JOIN default_messages msg ON p.user_id = msg.sender_user_id 
JOIN default_recipient rcp ON msg.id = rcp.message_id
WHERE rcp.user_id = 1 
 AND rcp.deleted = 0
ORDER BY msg.date DESC;

話雖如此，在您的範例數據中，我們的兩個查詢都返回相同的結果。或許全集有不同的數據。

引用自：https://dba.stackexchange.com/questions/20793