CODEWITHSUNDEEP
sql-server
Srinu
Srinu

Reputation: 151

How to get only matching data from three tables

Main Table: Select * from Table1

Id  Name1 Name2

101  ttt  sss

Second Table : Select * from Table2

SId  Id  Colum1 Column2  Column3

1    101  hhh     xxx    erre

2    101  wsa     tgf     fdfd

Third Table: Select * from Table3

TId  Id   TColumn1  Tcolumn2

5    101   uyt       uyu

My Query:

Select * from Table1 t1
Join Table2 t2 on t2.Id= t1.Id
Join Table3 t3 on t3.Id= t1.Id

I am geeting Data like this 

 Id  Colum1 Column2  Column3  TColumn1  Tcolumn2

 1   hhh     xxx    erre      uyt          uyu

 2   wsa     tgf     fdfd     uyt          uyu

What I want is of My data

 Id  Colum1 Column2  Column3  TColumn1  Tcolumn2

 1   hhh     xxx    erre      uyt          uyu

 2   wsa     tgf    fdfd     null         null

Upvotes: 0

Views: 60

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can use the below query 

 ; with cte as 
    (
    Select 
    Colum1,
    Column2,
    Column3,
    TColumn1,
    Tcolumn2,
    row_number () over(partition by t2.id order by SId asc) r
    from Table1 t1
    Join Table2 t2 on t2.Id= t1.Id
    Join Table3 t3 on t3.Id= t1.Id
    )

Select 
Colum1,
Column2,
Column3,
TColumn1 =case when r=1 then TColumn1 else NULL end,
Tcolumn2 =case when r=1 then Tcolumn2 else NULL end
from cte

See working demo

Upvotes: 1

Related Questions