Data Extraction

[altruist]

Hi there,

I am stuck with a SQL query. Not able to figure out how to go with it. I have got 3 tables.

a) Core_Client
Fields :
client_nbr, brn_nbr
b) Other_Client
Fields
client_nbr, brn_nbr, sub_nbr
c) Oper_geo
Fields
sur_geo_id,brn_nbr, sub_nbr

Join Relationship :

Table A is left joined to Table B based on client_nbr and brn_nbr
Table A is inner joined to Table C based on brn_nbr

other info :

i) each brn_nbr has more than one sub_nbr (hence different sur_oper_id for same brn_nbr)


Now heres the problem :

a) i need to fetch sur_oper_id
b) Table B may or may not have sub_nbr for the corresponding brn_nbr or even the entry of the brn_nbr with Table C
c) in case it does have sub_nbr value for brn_nbr then the corresponding sur_oper_id should be taken from table C. In all other case where sub_nbr or brn_nbr doesnt exist any value sur_oper_id can be taken (min or max) based on the join with Table A (i.e. the inner join with brn_nbr).

Would be great help if someone can guide.

Thank You

[ray.wurlod]

Does your particular SQL support a CASE construct?