SQL最好的方法在两个表格中找到所有结果，并带有标准

Question

im在SQL的初学者，希望您可以帮助我：

mysql Server 5

table1

----- ----------- ---------------- ------- |id |USERCODE |EntityCode |日期| ----- ----------- ---------------- ------- |X |Code123 |010011 |... ||X |Code123 |010012 |... ||X |Code123 |010045 |... | ----- ----------- ---------------- ------- 

table2

----- ----------- --------------- ------------------------------------------------------------------------------------------------------------------------ |id |USERCODE |EntityCode |其他field1 |其他field2 | ----- ----------- --------------- ------------------------------------------------------------------------------------------------------------------------ |X |Code123 |010011 |... |... ||X |Code123 |010012 |... |... ||X |Code123 |010045 |... |... ||.. |.. |.. |... |... | ----- ----------- --------------- ------------------------------------------------------------------------------------------------------------------------ 

实体表

------------- ------------------------ ------------------------------------------------- |EntityCode |其他field1 |其他field2 |Specialcode | ------------- ------------------------ ------------------------------------------------- |010011 |.. |.. |100 ||010012 |.. |.. |105 ||010045 |.. |.. |111 ||.. |.. |... |... | ------------- ------------------------ ------------------------------------------------- 

预期结果

约束：table1，table2和entity中存在的所有usercode，实体代码。specialCode= 105

------------ --------------- |USERCODE |EntityCode | ------------ --------------- |Code123 |010012 | ------------ --------------- 

Answer 1

SELECT a.USERCODE , a.ENTITYCODE
FROM table1 a 
JOIN table2 b ON a.USERCODE = b.USERCODE AND a.ENTITYCODE = b.ENTITYCODE
JOIN entity c ON a.ENTITYCODE = c.ENTITYCODE
WHERE c.SPECIALCODE = 105

Answer 2

尝试

SELECT USERCODE,ENTITYCODE FROM table1 INNER JOIN table2 ON table1.ENTITYCODE = table2.ENTITYCODE
INNER JOIN entity ON entity.ENTITYCODE = table1.ENTITYCODE
WHERE SPECIALCODE = 105

参考：

• sql-joins的视觉代表
• mysql-using-joins