MySQL联接表 [英] Mysql join tables

问题描述

我在数据库中有三个表.

I have three tables in the database.

第一个表是包含用户信息的表，它看起来像:

The first table is the one that contains user information and which looks like:

    id     |     name   |     status    
---------------------------------------
    1      |    john    |      1
    2      |    helen   |      1
    3      |    mike    |      1
    4      |    tina    |      1
    5      |    jim     |      0
    6      |    nina    |      1

第二个表包含某些服务的注册用户:

The second table contains registered users for some service:

   sid     |   status  
------------------------
    1      |     1
    2      |     1

第三张表包含其他服务的注册用户:

The third table contains registered users for other service:

   oid     |   status  
------------------------
    3      |     1
    4      |     1

我应该进行一个查询，该查询将从第二个和第三个表中找到状态为"1"的所有用户，然后从第一个表中获取所有这些用户的名称和ID(以及用户信息).该示例的结果如下所示:

I should make a query that will found all users with the status '1' from the second and third table and then get name and id of all those users from the first table (with users information). That result from this example would look like:

   id     |     name   |     status    
---------------------------------------
    1      |    john    |      1
    2      |    helen   |      1
    3      |    mike    |      1
    4      |    tina    |      1

该查询的外观如何?我应该使用INNER JOIN吗?

What will that query looks like? Should I use INNER JOIN?

推荐答案

INNER JOIN是正确的方法.

查询应为

SELECT user.id, user.name, user.status
FROM table1 AS user
INNER JOIN table2 AS service1 ON service1.sid = user.id
INNER JOIN table3 AS service2 ON service2.oid = user.id
WHERE service1.status = 1 AND service2.status = 1

如果您希望使用service1或service2的用户，则查询可能是

If you want users which are on service1 OR service2, the query could be

SELECT user.id, user.name, user.status
FROM table1 AS user
WHERE user.id IN (SELECT sid FROM table2 WHERE status=1) OR
      user.id IN (SELECT oid FROM table3 WHERE status=1)

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