mysql - 左连接无法正常工作

Question

我离开加入消息回复，进入主要消息，但是当我离开加入时，用户表没有被加入:

"SELECT messages.*, 
       message_replies.message_reply_message AS message_body 
FROM   messages 
       LEFT JOIN users 
              ON messages.message_user = users.user_id 
       LEFT JOIN message_replies 
              ON messages.message_id = message_replies.message_reply_main 
       LEFT JOIN user_personal_information 
              ON messages.message_user = 
                 user_personal_information.user_personal_information_user" .                   
                 $user . " " . $order . "" 

当我删除时:

messages.*, 
   message_replies.message_reply_message AS message_body 

然后只需选择 * 然后它就可以正常工作，但它们不包括我的消息回复，这是我的 php:

$messages = MessageModel::messages($user," WHERE message_user=? "," AND message_deleted=0 AND message_permdeleted=0   ORDER BY message_date DESC LIMIT 5");

和我的消息 sql:

CREATE TABLE IF NOT EXISTS `messages` (
  `message_id` int(11) NOT NULL,
  `message_user` int(11) NOT NULL,
  `message_subject` varchar(100) NOT NULL,
  `message_body` text NOT NULL,
  `message_to` int(11) NOT NULL,
  `message_read` int(1) NOT NULL DEFAULT '0',
  `message_date` datetime NOT NULL,
  `message_deleted` int(11) NOT NULL DEFAULT '0',
  `message_permdeleted` int(11) NOT NULL DEFAULT '0',
  `message_type` varchar(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `messages` (`message_id`, `message_user`, `message_subject`, `message_body`, `message_to`, `message_read`, `message_date`, `message_deleted`, `message_permdeleted`, `message_type`) VALUES
(1, 3, 'test', 'hello', 12, 1, '2015-10-12 02:09:51', 0, 0, 'sent'),

我的消息回复:

CREATE TABLE IF NOT EXISTS `message_replies` (
  `message_reply_id` int(11) NOT NULL,
  `message_reply_user` int(11) NOT NULL,
  `message_reply_main` int(11) NOT NULL,
  `message_reply_message` text NOT NULL,
  `message_reply_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;

INSERT INTO `message_replies` (`message_reply_id`, `message_reply_user`, `message_reply_main`, `message_reply_message`, `message_reply_date`) VALUES
(1, 3, 1, 'Hello, this is just a test reply\r\n', '2015-09-29 18:42:23'),

用户查询:

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL,
  `user_username` varchar(25) NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `user_password` varchar(255) NOT NULL,
  `user_enabled` int(1) NOT NULL DEFAULT '1',
  `user_staff` varchar(15) NOT NULL DEFAULT '',
  `user_account_type` varchar(20) NOT NULL DEFAULT '0',
  `user_registerdate` date NOT NULL,
  `user_twofactor` int(11) NOT NULL DEFAULT '0',
  `user_twofackey` varchar(255) NOT NULL,
  `user_forgot_email_code` varchar(255) NOT NULL,
  `user_emailverified` varchar(25) NOT NULL DEFAULT 'unverified',
  `user_banned` varchar(25) NOT NULL DEFAULT 'unbanned',
  `user_has_avatar` int(11) NOT NULL DEFAULT '0',
  `user_has_banner` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `user_username`, `user_email`, `user_password`, `user_enabled`, `user_staff`, `user_account_type`, `user_registerdate`, `user_twofactor`, `user_twofackey`, `user_forgot_email_code`, `user_emailverified`, `user_banned`, `user_has_avatar`, `user_has_banner`) VALUES
(3, 'lol', 'email@mail.com', '$2y$10$jjTLGiOC2XtwhzRrLOq15euw4S0jXmWveEctd9pYEL44LEt3Vdfa2', 1, 'admin', 'Business', '2015-07-21', 0, '5GILYNBWBXVAUV3A', 'd71a30cb75faed7c48cba971cf934922', 'unverified', 'unbanned', 1, 1),

那么我怎样才能让我的 sql 与上面提供的信息一起工作

变量转储:

array(5) { [0]=> object(stdClass)#22 (10) { ["message_id"]=> string(2) "10" ["message_user"]=> string(1) "3" ["message_subject"]=> string(8) "yooooooo" ["message_body"]=> string(5) "fffff" ["message_to"]=> string(2) "12" ["message_read"]=> string(1) "1" ["message_date"]=> string(19) "2015-10-12 03:36:32" ["message_deleted"]=> string(1) "0" ["message_permdeleted"]=> string(1) "0" ["message_type"]=> string(8) "recieved" } [1]=> object(stdClass)#23 (10) { ["message_id"]=> string(2) "10" ["message_user"]=> string(1) "3" ["message_subject"]=> string(8) "yooooooo" ["message_body"]=> string(3) "lol" ["message_to"]=> string(2) "12" ["message_read"]=> string(1) "1" ["message_date"]=> string(19) "2015-10-12 03:36:32" ["message_deleted"]=> string(1) "0" ["message_permdeleted"]=> string(1) "0" ["message_type"]=> string(8) "recieved" } [2]=> object(stdClass)#24 (10) { ["message_id"]=> string(1) "9" ["message_user"]=> string(1) "3" ["message_subject"]=> string(8) "jhjhjhjh" ["message_body"]=> NULL ["message_to"]=> string(2) "12" ["message_read"]=> string(1) "1" ["message_date"]=> string(19) "2015-10-12 03:34:54" ["message_deleted"]=> string(1) "0" ["message_permdeleted"]=> string(1) "0" ["message_type"]=> string(4) "sent" } [3]=> object(stdClass)#25 (10) { ["message_id"]=> string(1) "8" ["message_user"]=> string(1) "3" ["message_subject"]=> string(8) "jhjhjhjh" ["message_body"]=> NULL ["message_to"]=> string(2) "12" ["message_read"]=> string(1) "0" ["message_date"]=> string(19) "2015-10-12 03:34:40" ["message_deleted"]=> string(1) "0" ["message_permdeleted"]=> string(1) "0" ["message_type"]=> string(4) "sent" } [4]=> object(stdClass)#26 (10) { ["message_id"]=> string(1) "7" ["message_user"]=> string(1) "3" ["message_subject"]=> string(2) "yo" ["message_body"]=> NULL ["message_to"]=> string(2) "12" ["message_read"]=> string(1) "0" ["message_date"]=> string(19) "2015-10-12 03:33:17" ["message_deleted"]=> string(1) "0" ["message_permdeleted"]=> string(1) "0" ["message_type"]=> string(4) "sent" } }

user_personal_information

CREATE TABLE IF NOT EXISTS `user_personal_information` (
  `user_personal_information_id` int(11) NOT NULL,
  `user_personal_information_user` int(11) NOT NULL,
  `user_firstname` varchar(75) NOT NULL,
  `user_surname` varchar(75) NOT NULL,
  `user_birthdate` date NOT NULL,
  `user_age` int(11) NOT NULL,
  `user_gender` varchar(25) NOT NULL,
  `user_contactemail` varchar(75) NOT NULL,
  `user_telephone` varchar(75) NOT NULL,
  `user_mobile` varchar(75) NOT NULL,
  `user_introduction` text NOT NULL,
  `user_occupation` varchar(75) NOT NULL,
  `user_relocate` varchar(75) NOT NULL,
  `user_available` varchar(75) NOT NULL,
  `user_fax` varchar(100) NOT NULL,
  `user_street` varchar(100) NOT NULL,
  `user_zip` varchar(100) NOT NULL,
  `user_city` varchar(100) NOT NULL,
  `user_state` varchar(100) NOT NULL,
  `user_country` varchar(75) NOT NULL,
  `user_personal_information_chestsize` varchar(11) NOT NULL,
  `user_personal_information_waistsize` varchar(11) NOT NULL,
  `user_personal_information_bootsize` varchar(11) NOT NULL,
  `user_personal_information_harness_size` varchar(11) NOT NULL,
  `user_personal_information_inside_leg` varchar(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user_personal_information`
--

INSERT INTO `user_personal_information` (`user_personal_information_id`, `user_personal_information_user`, `user_firstname`, `user_surname`, `user_birthdate`, `user_age`, `user_gender`, `user_contactemail`, `user_telephone`, `user_mobile`, `user_introduction`, `user_occupation`, `user_relocate`, `user_available`, `user_fax`, `user_street`, `user_zip`, `user_city`, `user_state`, `user_country`, `user_personal_information_chestsize`, `user_personal_information_waistsize`, `user_personal_information_bootsize`, `user_personal_information_harness_size`, `user_personal_information_inside_leg`) VALUES
(1, 3, 'name', '123', '0000-00-00', 0, '', '', '07000', '00000', 'hello\r\n', 'Looking for work', '', '', '', '  jfkfkfjk', 'kjkjkjkj', 'kjkjkjk', 'kjkjk', 'United Kingdom', '123', '0', '0', '0', '0'),

Answer 1

为消息表使用表别名并尝试:

SELECT ms.*, 
       ms_r.message_reply_message AS message_body 
FROM   messages AS ms
       LEFT JOIN users AS u
              ON ms.message_user = u.user_id 
       LEFT JOIN message_replies AS ms_r
              ON ms.message_id = ms_r.message_reply_main 
       LEFT JOIN user_personal_information AS u_p_i
              ON ms.message_user = u_p_i.user_personal_information_user
       WHERE ms.message_user=3 AND ms.message_deleted=0 AND ms.message_permdeleted=0 
       ORDER BY ms.message_date DESC LIMIT 5

SEE HERE实例