SQL通过公共列中的精确组合连接两个表

I am trying to match two tables that have two columns; one with id number the other with type number.

我试图匹配两个有两列的表;一个带有id号,另一个带有型号。

In the first table id number can have different type numbers for example id 1 can have assigned type 1,2,3,4 in the second table id 1 can have assigned type 1,2,4 ... I would like to join the tables by id only if types are exactly the same ...

在第一个表中,id号可以有不同的类型号,例如id 1可以在第二个表中分配类型1,2,3,4 id 1可以分配类型1,2,4 ...我想加入只有在类型完全相同的情况下才能通过id表...

Thanks

1 个解决方案

#1

Based on your edit, you need an INNER JOIN based on the combination of id and type in both tables:

根据您的编辑,您需要基于两个表中id和type的组合的INNER JOIN:

SELECT
   Table1.id
   , Table1.type
   , Table1.ColumnsYouNeed
   , Table2.ColumnsYouNeed
FROM
    Table1
    INNER JOIN Table2 ON Table1.id = Table2.id 
        AND Table1.type = Table2.type

This would ensure that you only see those rows from both tables where the ID and Types match.

这将确保您只能看到ID和类型匹配的两个表中的那些行。

Example: If Table1 contains id = 12345 and type = 'foo', and Table2 has id = 12345 and type = 'foo', you will see the data (based on the respective columns you add to the SELECT statement) from both Table1 and Table2.

示例:如果Table1包含id = 12345并且type ='foo',并且Table2具有id = 12345并且type ='foo',则您将从Table1和Table1中看到数据(基于您添加到SELECT语句的相应列)表2。

If Table1 contains id = 12345 and type = 'foo', but Table2 has id = 12345 and only type = 'bar', you will not see either rows.

如果Table1包含id = 12345并且type ='foo',但Table2的id = 12345并且只有type ='bar',则不会看到任何行。

Should you want to see all values in Table1, regardless of whether they are match in Table2, change INNER JOIN to LEFT JOIN. Should you want to show all values from Table2, regardless of whether or not they exist in Table1, you could change INNER JOIN to RIGHT JOIN.

如果要查看Table1中的所有值,无论它们是否与表2中的匹配,请将INNER JOIN更改为LEFT JOIN。如果要显示Table2中的所有值,无论它们是否存在于Table1中,您都可以将INNER JOIN更改为RIGHT JOIN。

* UPDATE based on your comment *

*根据您的评论更新*

I figured I'd show some additional examples of how JOINs work.

我想我会展示一些JOIN如何工作的其他例子。

Let's start by setting up some sample tables with sample data:

让我们首先设置一些包含样本数据的样本表:

CREATE TABLE #table1 (id tinyint, myType tinyint, columnA varchar(5));

INSERT INTO #table1 
VALUES (1, 1, 'aaaa'), (1, 2, 'aaab'), (1, 4, 'aaaac'), (2, 1, 'bbbbb'), (2,2, 'bbbbc'), (3, 1, 'dddd');

CREATE TABLE #table2 (id tinyint, myType tinyint, columnB varchar(25));

INSERT INTO #table2
VALUES (1, 1, 'Match for id 1'), (1, 2, 'Second match for id 1'), (1, 4, 'Match for id 1'), (2, 1, 'Match for id 2'), (2,3, 'Not a match for id 2'),  (4, 1, 'fdvg');

As you can see, all entries for id 1 exist in both tables. For id 2, #Table1 and #table2 both have type = 1, but type 2 and 3 only exist in one table (and thus should not show up in the final result). Id 3 only exists in #table1, and thus also won't show up. Id 4 only exists in #table2, and thus also shouldn't show.

如您所见,id 1的所有条目都存在于两个表中。对于id 2,#Table1和#table2都具有type = 1,但是类型2和3仅存在于一个表中(因此不应出现在最终结果中)。 Id 3仅存在于#table1中,因此也不会显示。 Id 4仅存在于#table2中,因此也不应该显示。

Let's start by just joining the tables on id, without taking types into consideration:

让我们首先加入id上的表,而不考虑类型:

SELECT 
    *
FROM #table1
    JOIN #table2 ON #table1.id = #table2.id;

Result:

id  myType  columnA id  myType  columnB
1   1   aaaa    1   1   Match for id 1
1   2   aaab    1   1   Match for id 1
1   4   aaaac   1   1   Match for id 1
1   1   aaaa    1   2   Second match for id 1
1   2   aaab    1   2   Second match for id 1
1   4   aaaac   1   2   Second match for id 1
1   1   aaaa    1   4   Match for id 1
1   2   aaab    1   4   Match for id 1
1   4   aaaac   1   4   Match for id 1
2   1   bbbbb   2   1   Match for id 2
2   2   bbbbc   2   1   Match for id 2
2   1   bbbbb   2   3   Not a match for id 2
2   2   bbbbc   2   3   Not a match for id 2

As you can see, this will return a list of every row for which the ids exist in both tables, not taking types into account. This results in a result set where the combination of types are split out across rows. This is clearly not what we are after.

如您所见,这将返回两个表中存在ID的每一行的列表,而不考虑类型。这会产生一个结果集,其中类型组合跨行分割。这显然不是我们所追求的。

Next step is to add the types, and only return those rows where both the id and type exist in both tables:

下一步是添加类型,并仅返回两个表中都存在id和type的行:

SELECT 
    *
FROM #table1
    JOIN #table2 ON #table1.id = #table2.id
        AND #table1.myType = #table2.myType;

Result:

id  myType  columnA id  myType  columnB
1   1   aaaa    1   1   Match for id 1
1   2   aaab    1   2   Second match for id 1
1   4   aaaac   1   4   Match for id 1
2   1   bbbbb   2   1   Match for id 2

Much better. Now we have a list of every combination that exists in both tables. However, as per your description, you do not want to see id = 2, because there's no full match on the types between the tables.

好多了。现在我们列出了两个表中存在的每个组合。但是,根据您的描述,您不希望看到id = 2,因为表之间的类型没有完全匹配。

The above query already weeds out any rows existing in #table1 that do not exist in #table2 (id = 2, type = 2 is not returned), but obviously it did not take into account there is a row for in #table2 that does not exist in #table1.

上面的查询已经清除#table1中存在的#table2中不存在的任何行(id = 2,不返回type = 2),但很明显它没有考虑到#table2中有一行#table1中不存在。

We can accomplish that by adding a subquery listing all ids for which an entry in #table2 exists that does not exist in #table1:

我们可以通过添加一个子查询来实现这一点,该子查询列出了#table1中存在的#table1中不存在的条目的所有id:

SELECT 
    *
FROM #table1
    JOIN #table2 ON #table1.id = #table2.id
        AND #table1.myType = #table2.myType
WHERE #table1.id NOT IN 
        (
            SELECT #Table2.id FROM #Table2 
                LEFT JOIN #table1 ON #table2.id = #table1.id 
                    AND #Table2.myType = #table1.myType
            WHERE #table1.MyType IS NULL
        );

Result:

1   1   aaaa    1   1   Match for id 1
1   2   aaab    1   2   Second match for id 1
1   4   aaaac   1   4   Match for id 1

The start of the query is the same as before. However, now there is an additional WHERE clause. The subquery following that basically will give back and id for which a combination of id and type in #table2 does not have a matching record in #table1.

查询的开头与之前相同。但是,现在还有一个额外的WHERE子句。后面的子查询基本上将返回和id,#table2中的id和type的组合在#table1中没有匹配的记录。

If you were to run that subquery by itself, it would return ids 2 and 4 (4 only exists in #Table2, where id = 2, type = 3 is not in #table1.

如果你自己运行那个子查询,它将返回id 2和4(4只存在于#Table2中,其中id = 2,type = 3不在#table1中。