All Forums Database
Arun1377 11 posts Joined 09/13
27 Oct 2013
fetching data by joining 2 tables in a view

Hi,
I need to fetch data by joining 2 tables as shown below:
I have 2 tables T1 and T2. T1 have fields A,B,C,D and X. The table T2 have fields E,F,G and H. I need to join tables with the fields A,B and C of T1 with E,F and G of T2 and fetch the field H. And then i need to join the tables using the fields B,C and D of T1 with F,G and E of T2 and need to fetch the field H.
Note: the values of field A and D of T1 is present in the column E of T2 in different records, and the scenario is i need to pull the value of H for the 2 different join conditions and having it in same record. The select statement is to be used in a view definition.
Kindly pour in your ideas.

Arun1377 11 posts Joined 09/13
30 Oct 2013

Hi Experts,
Kindly help me to sort this out.
Regards,
Arun

M.Saeed Khurram 544 posts Joined 09/12
30 Oct 2013

Hi Arun,
Can you please paste some sample data, 2-3 rows from each table?
I have little confusion about the line " the values of field A and D of T1 is present in the column E of T2 in different records"
 
 

Khurram

VandeBergB 182 posts Joined 09/06
30 Oct 2013

You can do this by aliasing T2 with two different aliases
select
 tb2a.H as column1
,tb2b.H as column2
from table1 t1 inner join table2a t2a on (t1.a=t2a.e and t1.b=t2a.f and t1.c = t2a.g)
inner join table2b
on ((t1.b=t2b.f and t1.c=t2a.g and t1.d = t2a.e)

Some drink from the fountain of knowledge, others just gargle.

Arun1377 11 posts Joined 09/13
13 Nov 2013

Hi VandeBerg,
Your suggestion is working fine :).
But i am have 15 to 18 such different join combinations between the two tables, so i am joining the table T2 18 times with different alias name and fetching the required columns. But since i am doing this in a view, the performance of the view is very low. Is there any way to optimize this or any other way to achieve it without affecting the view performance.
Thank you!
 
Regards,
Arun

ulrich 816 posts Joined 09/09
13 Nov 2013

it sounds you have some kind of combinatoric problem.
Can you explain why the data is stored in this way and what you achieve with these joins?

For a chance to understand your problem you would need to share the DDLs.
How many rows are in the tables?
Do you join with 18 columns?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Arun1377 11 posts Joined 09/13
18 Nov 2013

Hi Ulrich,
I am in need to adding new fieds in the table T2 to table T1. I am trying to transpose the records in the table T2 to columns in T1. So it requires me around 15 joins.
Regards,
Arun

ulrich 816 posts Joined 09/09
19 Nov 2013

But this might be done more efficiently with an aggregation T2 before joining to T1.
But again if you can not share some example DDLs and example data  (please as insert statements) it will be very difficult to help you.
Ulrich

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Arun1377 11 posts Joined 09/13
19 Nov 2013

Hi Ulrich,
PFB the DDLs , data in the table and the select statement in the view.
CREATE MULTISET TABLE DB_TEST.T1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
(
A DECIMAL(15,0),
B DECIMAL(15,0),
JOINkey1 INTEGER,
JOINkey2 INTEGER,
JOINkey3 INTEGER,
JOINkey4 INTEGER,
NO1 INTEGER,
NO2 INTEGER,
NO3 INTEGER,
NO4 INTEGER,
insert_dt DATE FORMAT 'YY/MM/DD')
PRIMARY INDEX ( A );

CREATE MULTISET TABLE DB_TEST.T2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
(
Dept_id INTEGER,
A INTEGER,
B INTEGER,
newfield CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( Dept_id );
Data in Table T1 is :
A B JOINkey1 JOINkey2 JOINkey3 JOINkey4 NO1 NO2 NO3 NO4 insert_dt
1234 1111 12 13 14 15 16 17 18 19 11/19/2013
1235 1112 13 14 15 16 17 18 19 20 11/19/2013
1236 1113 14 15 16 17 18 19 20 21 11/19/2013
1237 1114 15 16 17 18 19 20 21 22 11/19/2013
1238 1115 16 17 18 19 20 21 22 23 11/19/2013
1239 1116 17 18 19 20 21 22 23 24 11/19/2013
1240 1117 18 19 20 21 22 23 24 25 11/19/2013

Data in table T2
 Dept_id A B newfield
111 12 16 ABCD 
222 13 17 EFGH 
333 14 18 IJKL 
444 15 19 MNOP 

I need the data to be displayed as:
A B newfield newfield newfield newfield
1234 1111 ABCD  EFGH  IJKL  MNOP 
1235 1112 EFGH  IJKL  MNOP  ?
1236 1113 IJKL  MNOP  ? ?
1237 1114 MNOP  ? ? ?
1238 1115 ? ? ? ?
1239 1116 ? ? ? ?
1240 1117 ? ? ? ?

Currently i am using the below SELECT statement in the view:
sel
T1. A ,
T1.B ,
T2.newfield,
T21.newfield,
T22.newfield,
T23.newfield
from DB_TEST.T1 T1
left outer join DB_TEST.T2 T2
on T1.JOINkey1 = T2.A and
T1.NO1 = T2.B
left outer join DB_TEST.T2 T21
on T1.JOINkey2 = T21.A and
T1.NO2 = T21.B
left outer join DB_TEST.T2 T22
on T1.JOINkey3 = T22.A and
T1.NO3 = T22.B
left outer join DB_TEST.T2 T23
on T1.JOINkey4 = T23.A and
T1.NO4 = T23.B
Kindly help me to improve the performance by avoiding the 4 joins over the same table.
--Arun

ulrich 816 posts Joined 09/09
20 Nov 2013

how many rows has your table t2?
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Arun1377 11 posts Joined 09/13
20 Nov 2013

Hi Ulrich,
The table T2 will have more than 80k records.
I have posted only sample data to explain the scenario.
 
--Arun

ulrich 816 posts Joined 09/09
21 Nov 2013

Ok, in this case it is too big to transform it into a SQL UDF.
You facing here the problem of an de-normalized design which leads to komplex queries.

CREATE MULTISET TABLE DB_TEST.T1
(
A DECIMAL(15,0),
B DECIMAL(15,0),
column_position byteint,
JOINkey INTEGER,
NO INTEGER
insert_dt DATE FORMAT 'YY/MM/DD')
PRIMARY INDEX ( A );

(where column_position (e.g. 1 to 4 based on your odering) would only be needed if it has a real meaning for you)
would be a straight forward input for your mapping as you could straight forward join the two tables.
If afterwards a de-normalized presentation of the data would be required you could "aggregate" the data by doing 

Seletc t1.a, 
       t1.b, 
       max(case when column_position = 1 then t2.newfiled else null end) as nf1,
       max(case when column_position = 2 then t2.newfiled else null end) as nf2,
       max(case when column_position = 3 then t2.newfiled else null end) as nf3,
       max(case when column_position = 4 then t2.newfiled else null end) as nf4
from t1 
     left outer join 
     t2
        on t1.joinkey = t2.a
           and t2.no = t2.b
group by t1.a,
         t2.a
;

Still not most elegant SQL but performance wise it should be much more efficient then doing so many left outer joins.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.