Hi Dimitar,
Thank you so very much!!! I never thought, someone would reply on a weekend.
I was exactly looking for this solution.
Thank you once again Sir! You saved my weekends!!! 😊
Thanks & Regards,
Sourav Biswas
+91-9650017306
From: Dimitar Draginov [mailto:dimitar.draginov@xxxxxxxxx]
Sent: 24 February 2018 23:20
To: biswas.sourav@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Need help in drafting SQL query
-- Here is the simplest and hopefully most powerful solution.
-- From what I've read it looks like table A is highest priority for retrieving
data, therefore avoiding any complexity I've just created an outer join of A
and C to main table B.
-- With NVL() I retrieve A data and only when a value is missing I retrieve B
data.
-- As seen this resolves your ID values of 1,2,7 restriction too.
-- NOTE 1: I assume that all columns are not null-able. Otherwise side effects
will occur..
-- NOTE 2: As seen I use the Oracle syntax for outer join..
select NVL(a.ID, c.ID) as ID,
NVL(a.FIN_ID,c.FIN_ID) as FIN_ID,
NVL(a.DATE,c.DATE) as DATE
from B, A, C
where a.FIN_ID(+) = b.FIN_ID
and c.FIN_ID(+) = b.FIN_ID;
Regards,
Dimitar
On Sat, Feb 24, 2018 at 8:31 AM, Sourav Biswas
<biswas.sourav@xxxxxxxxxxx<mailto:biswas.sourav@xxxxxxxxxxx>> wrote:
Hello Everyone,
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<tel:+91%2096500%2017306>
