SQL Server Select data from 2 tables conditionally [duplicate]

zyfwsgd6 于 2023-10-15 发布在其他

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

This question already has answers here:

T-SQL CASE Clause: How to specify WHEN NULL (18 answers)
Closed 24 days ago.

Let's say I have 2 tables (Table1 and Table2) with same structure

int Id
string Name

Ex:

Table1: (1,'abc'), (2,'xxx'), (3, 'mm')

Table2: (1,'abcd'), (3, 'nnnn')

The goal is to display data from Table1 if there is no data for the same Id in Table2 or from Table2 when there is such data

For the tables above, the data returned will be:

(1, 'abcd') -- from Table2

(2, 'xxx') -- from Table1

(3, 'nnnn') -- from Table2

This is what I tried:

select Table1.Id, 
case Table2.Name
    when null then Table1.Name
    else Table2.Name 
end as Name
from Table1
left join Table2 on Table2.Id = Table1.Id

but doesn't work

sql-server

来源：https://stackoverflow.com/questions/77146386/select-data-from-2-tables-conditionally

1条答案

按热度按时间

enxuqcxy1#

Your CASE WHEN is somewhat wrong, you need IS to check for NULL

CREATE TABLE Table1(id int, name varchar(5))

CREATE TABLE Table2(id int, name varchar(5))

INSERT INTO Table1 VALUES (1,'abc'), (2,'xxx'), (3, 'mm')

3 rows affected

INSERT INTO Table2 VALUES (1,'abcd'), (3, 'nnnn')

2 rows affected

select Table1.Id, 
case when Table2.Name is null
    then Table1.Name
    else Table2.Name 
end as Name
from Table1
left join Table2 on Table2.Id = Table1.Id

Id	Name
1	abcd
2	xxx
3	nnnn

fiddle

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Select data from 2 tables conditionally [duplicate]

1条答案

相关问题

热门标签

最新问答