如何在MySQL中使用空表联接表 [英] How to join tables in Mysql with empty table

问题描述

我有四个这样的表:

公司(标题，说明)

company_users(company_id，user_id)

company_users (company_id, user_id)

用户(id，group_id)

users (id, group_id)

用户个人资料(用户名，全名)

user_profiles (user_id, fullname)

我要列出广告系列.

我的代码是这样:

SELECT companies.*, user_profiles.fullname as company_contact
FROM companies
INNER JOIN company_users ON companies.id = company_users.company_id
INNER JOIN users ON company_users.user_id = users.id AND users.group_id=500
INNER JOIN user_profiles ON users.id = user_profiles.user_id;

当我添加新广告系列时，不会出现.因为company_id不在company_users中.

When i add a new campaign does not appear. Because company_id not in company_users.

我该如何解决?

注意:每个广告系列只能有一个用户.该用户的group_id为500.

Note: Eeach campaign must have only one user. This user's group_id is 500.

谢谢

表格:

companies
1   |   Test Company    |   Test Desciption
2   |   Test Company 2  |   Test Description 2



company_users
1   |   18  |   1
2   |   19  |   1

users
18  |   500     
19  |   900


user_profiles
18  |   18  |   Test Contact
19  |   19  |   Test User

左联接结果:

1   Test Company    Test Description    Test Contact
1   Test Company    Test Description    NULL
2   Test Company 2  Test Description 2  NULL

内部联接结果:

1   Test Company    Test Description    Test Contact

我需要这个:

1   Test Company    Test Description    Test Contact
1   Test Company 2  Test Description 2  NULL

推荐答案

听起来我们需要外部联接操作.

It sounds like we want outer join operations.

我们可以用关键字LEFT替换所有出现的关键字INNER.

We can replace all occurrences of the keyword INNER with the keyword LEFT.

然后，即使在company_users中找不到匹配的行，查询也会从companies返回行.

Then the query will return rows from companies even when no matching row is found in company_users.

编辑

响应提供其他信息的问题编辑.

In response to question edit that provides additional information.

如果我们要返回公司中的每一行以及相关用户的单行全名(如所需结果集中所示)，则可以在SELECT列表中使用相关子查询，而不是联接操作

If we want to return every row in companies, along with the fullname of a single row from a related user (as shown in the desired resultset), we could use a correlate subquery in the SELECT list, rather than a join operation.

例如:

  SELECT c.*
       , ( SELECT p.fullname
             FROM company_users cu
             JOIN users ON u.id = cu.user_id AND u.group_id=500
             JOIN user_profiles p ON p.user_id = u.id 
            WHERE cu.company_id = c.id
            ORDER BY p.fullname ASC
            LIMIT 1
         ) AS company_contact
    FROM companies c

子查询包含对companies中id列的引用.对于外部查询返回的每一行(companies中的每一行)，将使用companies行中的id值来执行SELECT列表中的子查询，

The subquery includes a reference to the id column in companies. For each row returned from the outer query (each row from companies), the subquery in the SELECT list is executed, using the id value from the companies row,

如果子查询找不到要返回的行，则返回NULL值. LIMIT 1子句确保我们最多返回一行(如果子查询返回多于一行，则将返回错误.)包括ORDER BY只是为了使结果更具确定性.也就是说，如果子查询碰巧返回了多行，则ORDER BY将对行进行排序，而LIMIT 1将获得已排序的行中的第一行.

If the subquery doesn't find a row to return, a NULL value is returned. The LIMIT 1 clause ensures that we return at most one row (if the subquery returns more than one row, there will be an error returned.) The ORDER BY is included just to make the result more deterministic. That is, if the subquery happens to return more than one row, the ORDER BY will sort the rows, and the LIMIT 1 will get the first of the sorted rows.

这篇关于如何在MySQL中使用空表联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！