Help required to construct a dynamic query

Transact-SQL
1

Hi All, I am struggling to create a query based on following requirement. Please help
For example I have four tables looking like this:
A_Master(Table1)
A_Master_ID BIGINT primary key
A_Column1

B_Master(Table2)
B_Master_ID BIGINT primary key
A_Master_ID BIGINT FK references A_Master(A_Master_ID)
B_Column1

C_Master(Table3)
C_Master_ID BIGINT primary key
B_Master_ID BIGINT FK references B_Master(B_Master_ID)
C_Column1

D_Master(Table3)
D_Master_ID BIGINT primary key
C_Master_ID BIGINT FK references C_Master(C_Master_ID)
D_Column1

Now the requirement is , At any point of time we will get only One Key value filled (i.e either A_Master_ID or B_Master_ID or C_Master_ID or D_Master_ID ) and based on that Key value I need to return all the matched data from All five tables. Please help on doing this.

Regards,
Mohan

2

try this

Select
A_Master_ID
,A_Column1
,B_Master_ID
,B_Column1
,C_Master_ID
,C_Column1
,D_Master_ID
,D_Column1
From A_Master a
inner Join B_Master b on a.A_Master_ID = b.B_Master_ID
Left Join C_Master c on b.B_Master_ID = c.C_Master_Id
Left Join D_Master d on c.C_Master_Id= d.D_Master_Id
Where
a.A_Master_ID ='abc' or b.B_Master_id = 'abc'
or c.C_Master_ID= 'abc'or d.D_Master_ID = 'abc'

3

Sounds to me like you want this (in this example, the id searched for is 123456):

select *
  from a_master as a
       full outer join b.master as b
               on b.a_master_id=a.master_id
       full outer join c.master as c
               on c.b_master_id=b.master_id
       full outer join d.master as d
               on d.c_master_id=c.master_id
 where 123456 in (a.master_id
                 ,b.master_id
                 ,c.master_id
                 ,d.master_id
                 )