SQL连接两个具有特定条件的表 [英] SQL join two tables with specific condition

问题描述

表A结构:

表B的结构:

上面是两个表TableB.TableARelationID是用于映射表A的RelationID.

Above are two tables, TableB.TableARelationID is a relationID which used to map table A.

所需的输出:

期望的结果是使用TableA.RecordID和TableB.Text，但仅使用表B中的类型2，即忽略类型1

The desired result would be taking TableA.RecordID and TableB.Text, but only of Type 2 in table B, i.e. ignore Type 1

下面是我使用的SQL查询:

Below is the SQL query which I used:

SELECT tablea.recordid, 
       tableb.text 
FROM   tablea 
       LEFT JOIN tableb 
              ON tablea.relationid = tableb.tablearelationid 
WHERE  type = 2 

但是上面的查询将输出:

But the above query would output:

即由于过滤了"where"子句，因此缺少RecordID 1.

i.e. RecordID 1 was missing, as the "where" clause filtered.

那么如何显示表A中的RecordID 1?

So how can I show RecordID 1 from Table A?

推荐答案

您需要将type = 2过滤器移至联接条件:

You need to move the type = 2 filter to the join condition:

SELECT  TableA.RecordID, TableB.Text 
FROM    TableA 
        LEFT JOIN TableB 
            ON TableA.RelationID = TableB.TableARelationID 
            AND TableB.Type = 2;

考虑一下结果:

SELECT  TableA.RecordID, TableB.Text, TableB.Type
FROM    TableA 
        LEFT JOIN TableB 
            ON TableA.RelationID = TableB.TableARelationID;

您会得到

RecordID | Text | Type
  1      | NULL | NULL
  2      |   B  |  2
  3      |   C  |  2
  4      |   D  |  2

然后您在类型列上进行过滤，因此对于recordID = 1，您具有NULL = 2的位置，该位置为假(它实际上不是假的，它为空，但它不是真)，因此该记录从最终结果.

Then you are filtering on the type column, so for recordID = 1 you have where NULL = 2 which is false (it is not actually false, it is null, but it is not true), so this record is elimitated from the final result.

无论何时离开联接，都必须在联接条件(而不是where)中应用希望应用于左表的所有过滤条件，否则您可以有效地将其转换为内部联接.

Whenever you left join you must apply any filtering criteria you wish to apply to the left table in the join condition not the where, otherwise you effectively turn it into an inner join.

这篇关于SQL连接两个具有特定条件的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！