SQL Server Conditionally JOIN two tables to a third, based on two columns in the third table?

dsekswqp 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(112)

I have a Table "C" that is meant to interlink two other tables, Table A and Table B. They have the general forms:

Table A:
| Id | Value |
| ------------ | ------------ |
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |

Table B:

Id	Value
1	4
2	6
3	8

Table C has two columns that are either NULL or a foreign key for tables A or B.

Table C:
| Key | Table_A_Id | Table_B_Id |
| ------------ | ------------ | ------------ |
| 1 | 1 | NULL |
| 2 | NULL | 2 |
| 3 | 2 | NULL |
| 4 | NULL | 1 |

Is there a way to join these tables together such that the output is:

Key	Value
1	10
2	6
3	20
4	4

When I have had these issues in the past I have usually run two separate queries and post-processed the data in R, but I was curious if there is a way that this can be done natively in SQL. If I just LEFT JOIN Tables A and B to C then it would create two different Value columns.

sql-server

来源：https://stackoverflow.com/questions/77406106/conditionally-join-two-tables-to-a-third-based-on-two-columns-in-the-third-tabl

1条答案

按热度按时间

6mzjoqzu1#

When asking questions, it's really helpful to provide the DDL/DML, in addition to the example data:

DECLARE @TableA TABLE (Id INT, Value INT);
INSERT INTO @TableA (ID, Value) VALUES
(1, 10), (2, 20), (3, 30);
DECLARE @TableB TABLE (Id INT, Value INT);
INSERT INTO @TableB (Id, Value) VALUES
(1, 4), (2, 6), (3, 8);

DECLARE @TableC TABLE ([Key] INT, TableA_ID INT, TableB_ID INT);
INSERT INTO @TableC ([Key], TableA_ID, TableB_ID) VALUES
(1, 1, NULL), (2, NULL, 2 ), (3, 2, NULL), (4, NULL, 1);

Using that:

SELECT c.[Key], COALESCE(a.Value,0) + COALESCE(b.value,0) AS Value
  FROM @TableC c
    LEFT OUTER JOIN @TableA a
      ON c.TableA_ID = a.Id
    LEFT OUTER JOIN @TableB b
      ON c.TableB_ID = b.Id;

Key	Value
1	10
2	6
3	20
4	4

What are we doing here? TableC is the common table, from there we can LEFT OUTER JOIN the other two tables, so we get rows back whether there is a match or not. Then, we COALESCE (take the first non-null value, alternatively ISNULL ) each of the value columns with a literal 0 and add the results together. Finally, we return the [Key] column (in braces as it's a reserved word) and the result of the COALESCE for each row in TableC .

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Conditionally JOIN two tables to a third, based on two columns in the third table?

1条答案

相关问题

热门标签

最新问答