Need help in drafting SQL query

I need some help in drafting a query. We have below mentioned 3 tables (I've limited the columns for brevity).

Column FIN_ID is the primary key on Table B and Table A and C, has respective foreign keys.

FIN_ID column in Table A and C have 3 values in common, i.e. 1,2,7.

So, when I join these 3 tables, I want the output to display ID, FIN_ID and DATE columns from Table A and C. However, when FIN_ID is used in predicate, and value matches either 1 or 2 or 7 and, also, these values are present in Table A, then all 3 columns should be fetched from Table A, or else, it should get the details from Table C.

For rest of the unique values of FIN_ID, it should fetch rows from respective tables. Table A

Table B

Table C

id

fi_ID (FK)

date

fin_ID (PK)

id

fin_ID (FK)

date

a1

1

01-01-2018

1

c1

1

01-01-2017

a2

2

02-01-2018

2

c2

2

02-01-2017

a3

4

03-01-2018

3

c3

3

03-01-2017

a4

7

04-01-2018

4

c4

5

04-01-2017

a5

8

05-01-2018

5

c5

6

05-01-2017

6

c6

7

06-01-2017

7

c7

9

07-01-2017

8

c8

10

08-01-2017

9

10

NOTE: We are using 11.2.0.4 database.

Thanks & Regards,
Sourav Biswas
+91-9650017306

--
http://www.freelists.org/webpage/oracle-l