从mysql中的多个表中获取特定结果

This is kinda confusing, therefore I will explain my goal step by step, Below are the tables that I am working on (First Table, Second Table, and Third Table). First, I need to verify if the username from the Second Table does exist on the First Table, if not, remove the one that has no common username

这有点令人困惑,因此我将逐步解释我的目标,下面是我正在处理的表格(第一表,第二表和第三表)。首先,我需要验证第一个表中的用户名是否确实存在于第一个表中,如果没有,请删除没有通用用户名的用户名

The outcome should be something like this (George was excluded since it has no common username to First Table):

结果应该是这样的(乔治被排除在外,因为它没有第一个表的常用用户名):

user_id | username
5423    | Bob
43      | Vicky

THEN I would like to verify by comparing the previous results above and the Third Table. My goal is to check if the user_id AND username from the previous results above has a common rows on the Third Table, if it does, EXCLUDE that one, and only retrieve the unique ones.

那么我想通过比较上面的结果和第三表来验证。我的目标是检查上面结果中的user_id AND用户名是否在第三个表上有一个公共行,如果是,则排除那个,并且只检索唯一的行。

The OVERALL results should be the one below since 5423 | Bob does not exist yet on the Third Table:

OVERALL结果应该是5423以来的结果鲍勃在第三桌上尚不存在:

OVERALL RESULTS:

user_id | username
5423    | Bob

First Table:

| username
| Bob
| Jessie
| Vicky

Second Table:

user_id | username
5423    | Bob
123     | Georgie
43      | Vicky

Third Table:

user_id | username
1       | Luke
54      | Stephenie
43      | Vicky

2 个解决方案

#1

SELECT  b.*
FROM    FirstTable a
        INNER JOIN SecondTable b
            ON  a.username = b.username
        LEFT JOIN ThirdTable c
            ON  b.user_ID = c.user_ID 
WHERE   c.user_ID IS NULL

• SQLFiddle Demo

The first join uses INNER JOIN so that only records that matches the condition will be on the result list. The second join uses LEFT JOIN so that all records from the left hand side table whether it has a matching row or not will be shown on the result list. Any non-matching rows will have a NULL value on the columns of the right hand side table.

第一个连接使用INNER JOIN,以便只有匹配条件的记录才会出现在结果列表中。第二个连接使用LEFT JOIN,以便左侧表中的所有记录是否具有匹配的行将显示在结果列表中。任何不匹配的行将在右侧表的列上具有NULL值。

Since you want to get only non matching records on the final result, you need to need to have WHERE clause that filters NULL values on the right hand side table.

由于您希望只获得最终结果的非匹配记录,因此您需要使用WHERE子句来过滤右侧表中的NULL值。

#2

Try this:

SELECT  b.*
FROM    FirstTable a
        INNER JOIN SecondTable b
            ON  a.username = b.username
        LEFT JOIN ThirdTable c
            ON  b.user_ID = c.user_ID 
WHERE   b.`username` <> c.`username`