SQL to check if a value exists or not.

Question

I have 3 tables - TableA , TableB and TableDetail

 **Table Detail** id FinalValue 1 99 2 88 3 77 **Table A** id InitValue Num 1 10 100 2 30 200 **Table B** id InitValue Num 3 20 200 

My requirement is to get FinalValue from TableDetail based

 Num 

( Num value will be selected by the user from the TextBox user interface). If table B has an entry for partcular Num, then use the identifier of table B. If not, use the identifier of table A. It is assumed that table A has entries for all Num values.

For instance,

 If Num=200: 

Since the record exists in TableB for Num=200 , use the TableB corresponding to id (3) and get FinalValue from TableDetail, where id = 3, i.e.

 FinalValue = 77 If Num =100: 

Since TableB has no entry for Num=100 , use TableA corresponding to id (1) and get FinalValue from TableDetail, where id = 1, i.e.

 FinalValue = 99 

Can you help me create a PL / SQL query for the same. Thanks!

Answer 1

try the following: @num is passed by user

 select FinalValue from TableDetail where id in (select isnull(T2.id,T1.id) from TableA T1 on T.id = T1.id and T1.Num = @num left join TableB T2 on T.id = T2.id and T2.Num = @num) 

Answer 2

Replacing 200 with a value from the user interface, you can do this as follows:

 declare @id int select @id = id from TableB where Num = 200 if(@id is null) begin select @id = id from TableA where id = 200 end select FinalValue from Detail where id = @id