Mysql在多种情况下左联接 [英] Mysql Left Join on multiple conditions

问题描述

我有两个表，如下所示: 用户表:

I have two tables as below: User table:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)      
);

还有另一个表，如uep所示:

And another table as uep which as below:

CREATE TABLE `uep` (
  `user_id` int(10) unsigned NOT NULL,
  `email_template_id` int(10) unsigned NOT NULL,
  `sid` int(10) unsigned NOT NULL,
  `send_email` tinyint(1) NOT NULL DEFAULT '1'
);

我想从用户表中获取所有记录，并且仅在
时才将left联接到uep表中 user.id = uep.user_id AND uep.send_email = 0

I want to get all records from user table and do left join to uep table only when
user.id = uep.user_id AND uep.send_email = 0

但是当我运行查询

Select id, group_concat(email_template_id) as T from user
LEFT join uep ON (user.id = uep.user_id AND uep.send_email = 0)
group by user_id limit 100

它从用户表中忽略具有send_email = 1的用户.

It ignore user from user table which are having send_email = 1.

我只想在user.id和send_email = 0时加入uep表，否则就不应该加入并仅返回用户详细信息.

I want to join uep table only when user.id and send_email =0 else it should be not join and return only user details.

Sql Fiddle: http://www.sqlfiddle.com/#!2/1f083 /2

Sql Fiddle :http://www.sqlfiddle.com/#!2/1f083/2

任何需要帮助的人！

推荐答案

尝试

Select 
    id, group_concat(email_template_id) as T 
from 
    user
LEFT join 
    uep 
ON 
  (user.id = uep.user_id AND uep.send_email = 0)
OR
  (uep.send_email = 1 AND user.id = uep.user_id)
group by 
  user_id 
limit 
  100;

在 http://www.sqlfiddle.com/#!2/6c28ee/上的

demo 1

这篇关于Mysql在多种情况下左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！