fetching data by joining 2 tables in a view

Database
Enthusiast

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.

11 REPLIES
Enthusiast

Re: fetching data by joining 2 tables in a view

Hi Experts,

Kindly help me to sort this out.

Regards,

Arun

Enthusiast

Re: fetching data by joining 2 tables in a view

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
Enthusiast

Re: fetching data by joining 2 tables in a view

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)

Enthusiast

Re: fetching data by joining 2 tables in a view

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

Senior Supporter

Re: fetching data by joining 2 tables in a view

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?
Enthusiast

Re: fetching data by joining 2 tables in a view

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

Senior Supporter

Re: fetching data by joining 2 tables in a view

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

Enthusiast

Re: fetching data by joining 2 tables in a view

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

Senior Supporter

Re: fetching data by joining 2 tables in a view

how many rows has your table t2?