在SQL上匹配2个具有多个重复值的表

I have 2 tables as follows -

我有2个表如下 -

Table 1 -

表格1 -

Catalog Number| Section|    Qty|     Status
---
123|                A|          3|        New
---
123|                B|          2|        New
---
123|            C|          1|        New
---
456|                A|          3|        Old|
--- 
456|                B|          1|        Old|
-- 
456|             C|          2|         Old|
--

Table 2:

表2：

Catalog Number| Section|    Qty|     Status
---
123|                A|          3|        New
---
123|            B|          2|        New
---
123|                C|          1|        New
---
123|            D|          3|        New
---
456|            A|          3|        Old
---
456|             B|           1|         Old
---

I want a result that is something like this -

我想要一个像这样的结果 -

Catalog Number| Section| Qty|   Status|   Catalog Number| Section| Qty| Status|
---
123|          A|            3|        New|      123|            A|      3|  New
---
123|          B|        2|        New|      123|            B|      2|   New
---
123|          C|            1|     New|     123|            C|       1|   New
---
Null|            Null|        Null|      Null|    123|        D|      3|   New
---
456|                A|          3|        Old|      456|      A|       3|  Old
---
456|             B|           1|         Old|      456|       B|       1|   Old
---
456|             C|           2|         Old|     Null|     Null|   Null| Null
---

I have tried using SQL Joins and have not gotten anywhere. Any help would be greatly appreciated. Thanks!!

我尝试过使用SQL连接但没有得到任何结果。任何帮助将不胜感激。谢谢！！

Edit -

编辑 -

This is the query I used:

这是我使用的查询：

SELECT * 
FROM Table1 a
INNER JOIN Table2 b ON a.CatalogNumber = b.CatalogNumber

3 个解决方案

#1

Are you looking for a FULL OUTER JOIN?

你在寻找一个完整的外部联盟吗？

SELECT a.CatlogNumber,
a.Section,
a.Qty,
a.Status,
b.CatlogNumber, 
b.Section,
b.Qty,
b.Status
FROM Table1 a
FULL OUTER JOIN Table2 b ON a.CatalogNumber = b.CatalogNumber;

This would show records in each table and NULL values where the tables don't have an equivalent record in the other table.

这将显示每个表中的记录和NULL值，其中表在另一个表中没有等效记录。

#2

You can use full outer join for this. The query goes like,

您可以使用完全外部联接。查询就像，

SELECT Table1.CatlogNumber, 
Table1.Section,Table1.Qty,Table1.Status,Table2.CatlogNumber, 
Table2.Section,Table2.Qty,Table2.Status,
FROM Table1
FULL OUTER JOIN Table2 ON Table1.CatlogNumber=Table2.CatlogNumber
ORDER BY Table1.CatlogNumber;

#3

I believe you need to perform a UNION of 2 separate queries. Something like this perhaps

我相信你需要执行2个单独查询的UNION。或许这样的事情

declare @table1 table(CatalogNumber int,Section varchar(100), Qty int, Status varchar(100) )
declare @table2 table(CatalogNumber int,Section varchar(100), Qty int, Status varchar(100) )
INSERT INTO @table1
SELECT 123, 'A', 3, 'New'
UNION SELECT 123, 'B', 2, 'New'
UNION SELECT 123, 'C', 1, 'New'
UNION SELECT 456, 'A', 3, 'Old'
UNION SELECT 456, 'B', 1, 'Old'
UNION SELECT 456, 'C', 2, 'Old'

INSERT INTO @table2
SELECT 123, 'A', 3, 'New'
UNION SELECT 123, 'B', 2, 'New'
UNION SELECT 123, 'C', 1, 'New'
UNION SELECT 456, 'D', 3, 'Old'
UNION SELECT 456, 'A', 3, 'Old'
UNION SELECT 456, 'B', 1, 'Old'


SELECT t1.*, t2.*FROM @table1 t1 LEFT JOIN @table2 t2 ON t1.CatalogNumber = t2.CatalogNumber and t1.Section=t2.Section and t1.Qty=t2.Qty and t1.Status=t2.Status
UNION
SELECT t1.*, t2.*FROM @table2 t2 LEFT JOIN @table1 t1 ON t1.CatalogNumber = t2.CatalogNumber and t1.Section=t2.Section and t1.Qty=t2.Qty and t1.Status=t2.Status

#1