CODEWITHSUNDEEP
sqlsql-servercase
user3202415
user3202415

Reputation: 117

How to return a respective value of column in a CASE statement in SQL Server

I'm trying to write a query for following.Lets say table A and table B has following columns as defined.

TableA(a,b,c,d,e,f,g,h) TableB(a,b,c,d,e,f,g,h,i,j,k) - This has a subset of rows of Table A.

declare @tableA table (a int, b int, c int, d int, e int, f int, g int, h int)
insert into @tableA values
(1,2,3,4,5,5,7,8),
(1,2,3,4,5,6,7,8),
(2,3,4,5,6,7,8,9),
(2,3,4,5,6,7,8,8),
(2,4,5,6,7,8,8,9),
(3,4,5,6,7,8,9,9)

declare @tableB table (a int, b int, c int, d int, e int, f int, g int, h int, i int, j int k int)
insert into @tableB values
(1,2,3,4,5,6,7,8,9,9,0),
(2,3,4,5,6,7,8,8,1,9,0),
(2,4,5,6,7,8,8,9,5,8,9),
(3,4,5,6,7,8,9,9,7,8,9)

Note that both tables have same columns and has duplicated rows. Now , i want a query which returns all rows in table A with an extra column (say 'iI' ) that would be NULL if this row not present in Table B else it returns the corresponding value of column 'i' matching row in table B. Then the query should return the output as following:

(1,2,3,4,5,5,7,8,NULL)

(1,2,3,4,5,6,7,8,9)

(2,3,4,5,6,7,8,9,NULL)

(2,3,4,5,6,7,8,8,1)

(2,4,5,6,7,8,8,9,5)

(3,4,5,6,7,8,9,9,7)

Can you please help me write the query . I tried to join these tables and used a case statement, but it throws me an error which says case statement cannot return multiple values

Upvotes: 1

Views: 374

Answers (2)

S3S
S3S

Reputation: 25112

declare @tableA table (a int, b int, c int, d int, e int, f int, g int, h int)
insert into @tableA values
(1,2,3,4,5,5,7,8),
(1,2,3,4,5,6,7,8),
(2,3,4,5,6,7,8,9),
(2,3,4,5,6,7,8,8),
(2,4,5,6,7,8,8,9),
(3,4,5,6,7,8,9,9)

declare @tableB table (a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int)
insert into @tableB values
(1,2,3,4,5,6,7,8,9,9,0),
(2,3,4,5,6,7,8,8,1,9,0),
(2,4,5,6,7,8,8,9,5,8,9),
(3,4,5,6,7,8,9,9,7,8,9)

select 
    a.*,
    b.i,
    b.j,
    b.k
from 
    @tableA a
left outer join
    @tableB b on a.a = b.a and a.b = b.b and a.c = b.c and a.d = b.d and a.e = b.e and a.f = b.f and a.g = b.g and a.h = b.h

Upvotes: 0

Stephan Lechner
Stephan Lechner

Reputation: 35154

I'd say a left outer join should solve your problem:

select a.*, b.i 
from TableA a left outer join TableB b on a.a = b.a and a.b = b.b ...

Note that a left outer join will contain all tuples from A, and the additional columns from B are filled with values if a corresponding tuple exists, or with NULL otherwise.

Upvotes: 2

Related Questions