代码之家 › 专栏 › 技术社区 › Jack

用Mysql连接3个表返回额外数据

jointable left-join mysql

Jack · 技术社区 · 8 年前

我有3张桌子-成员、活动、新

成员表:

+--------+---------+------------+
| userid | adminid | logindate  | 
+--------+---------+------------+
| test6  | test3   | 03/04/2016 |
| test7  | test3   |            |
+--------+---------+------------+

活动表格:

+----+--------+---------+---------+
| id | userid | adminid | city    |
+----+--------+---------+---------+
| 1  | test6  | test3   | Chicago |
+----+--------+---------+---------+

新建表格:

+----+--------+----------+
| id | userid | city     |
+----+--------+----------+
| 1  | test7  | New York |
+----+--------+----------+

我想找出属于管理员ID(test3)的所有成员,无论他们是活动的还是新的。mysql代码:

    SELECT active.id, active.userid, active.city, members.logindate,
 new.id as a, new.userid as b, new.city as c 
    FROM members
    LEFT JOIN active
    ON members.adminid = active.adminid
    LEFT JOIN new
    ON members.userid = new.userid
    WHERE members.adminid = 'test3'

我期望的是2行记录:

+----+--------+---------+------------+---+-------+----------+
| id | userid | city    | logindate  | a |  b    | c        |
+----+--------+---------+------------+---+-------+----------+
| 1  |test6   | Chicago | 03/04/2016 |   |       |          |
+----+--------+---------+------------+---+-------+----------+
|    |        |         |            | 1 | test7 | New York |
+----+--------+---------+------------+---+-------+----------+

相反,我得到了:

+----+--------+---------+------------+---+-------+----------+
| id | userid | city    | logindate  | a |  b    | c        |
+----+--------+---------+------------+---+-------+----------+
| 1  |test6   | Chicago | 03/04/2016 |   |       |          |
+----+--------+---------+------------+---+-------+----------+
| 1  |test6   | Chicago |            | 1 | test7 | New York |
+----+--------+---------+------------+---+-------+----------+

第一行(来自活动表)的数据在第二行重复。我做错了什么?感谢您的帮助。提前谢谢。

1 回复 | 直到 8 年前

sgeddes 8 年前

你应该 join 上 userid 而不是 adminid :

SELECT active.id, active.userid, active.city, members.logindate,
     new.id as a, new.userid as b, new.city as c 
FROM members
    LEFT JOIN active
        ON members.userid = active.userid
    LEFT JOIN new
        ON members.userid = new.userid
WHERE members.adminid = 'test3'

Condensed Fiddle Demo

推荐文章

Choppy The Lumberjack · 在左联接期间检查整行为空

6 年前

eternity1 · SAS-PROC SQL:两个表:每一列的不同值,左连接

6 年前

78282219 · 左连接返回空格

6 年前

mistaq · SQL Server-两边都有条件的条件左连接

6 年前

Imanali Mamadiev Ghazaleh Javaheri · Mysql计数连接表OneToMany

6 年前

Daniel M. Faccioli · 连接3行2个表并计数

6 年前

Josh · 带where条件子句的c上linq的左/外连接

6 年前

user2834566 · 为什么这个简单的左连接从不匹配的行返回数据?

6 年前

user3292755 · 按字符类型在R中左连接

6 年前

joe · 检索最近10个结果ASC Left Join MySql

6 年前