加入三个表以获得特定结果 [英] join three tables for specific result

问题描述

我有三个表:

表1 Users:

+----------+------------+------------+-------------------+
| ID [PK]  |  username  |  password  |  user_struct [FK] |
+----------+------------+------------+-------------------+
|       1  |  SAM       |       123  |  CIF              |
|       2  |  JACK      |       123  |  ADM              |
|       3  |  JAMAL     |       123  |  SDT              |
+----------+------------+------------+-------------------+

表2 CR_AR:

+-------------+-------+--------------+
| ID_CR [PK]  |  NUM  |  STRUCT [FK] |
+-------------+-------+--------------+
|          1  |   11  |  CIF         |
|          2  |   22  |  ADM         |
|          3  |   33  |  SDT         |
+-------------+-------+--------------+

表3 STRUCT:

+-----------------+--------------+
| STRUCTURE [PK]  |  description |
+-----------------+--------------+
| CIF             |  NULL        |
| IDM             |  NULL        |
| SDT             |  NULL        |
+-----------------+--------------+

我需要连接三个表，以便仅当所有三个表中的STRUCT值相同时才可以从CR_AR表中获取数据.

I need to join the three tables so I can get data from CR_AR table only when STRUCT value is the same in all three tables.

这是我编写的SQL查询，但是即使USERS中的user_struct不等于CR_AR

This is the SQL query I wrote but it seems to return all data even if user_struct in USERS is not equal to STRUCT in CR_AR

SELECT * FROM CR_AR AS C 
    LEFT JOIN STRUCT AS S ON S.STRUCTURE = C.STRUCT 
    LEFT JOIN USERS AS U ON U.USER_STRUCT = S.STRUCTURE

推荐答案

LEFT JOIN始终会从联接的左侧的表中返回所有记录，且仅那些与联接右中的表中的联接条件相匹配的记录中的值.

A LEFT JOIN will always return all records from the table on the left of the join, and only values from those records which match the join criteria from tables on the right of the join.

要仅返回已针对每个记录验证了合并条件的记录，则应使用INNER JOIN，即:

To only return records for which there join criteria is validated for every record, you should use an INNER JOIN, i.e.:

SELECT * 
FROM 
    CR_AR C 
    INNER JOIN STRUCT S ON S.STRUCTURE = C.STRUCT 
    INNER JOIN USERS U ON U.USER_STRUCT = S.STRUCTURE

这篇关于加入三个表以获得特定结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！