Hi
I am working on a query which is using mutiple left joins on the same table and has following data

Table 1

ID, Description 
1, 'AA'         
2, 'BB'          

-----------------------
REF1

id      REFID   REFNO
1        1        393
1        2         600
2        2        400

SINGLE id can have multiple REFIDs
---------------------------

REF2 Table

REFCODE  REFVALUE
20            china
31             USA
45             NY
48            Mexico
60             chile

------------------------
REF3 TABLE

REFID   REFCODE1 REFCODE2 REFCODE3 REFCODE4 REFCODE 5
1        20        60       45     NULL    NULL
1        48        45       NULL   NULL     NULL
2        20        31       60     NULL    NULL

REFID refers to REFID of REF1 table 
REFCODE refers to the REFCODE of REF2 tables
each REFID can have multiple refcode as shown in REF3 tables


The desired output is

ID, DESC, REFVALUE1,REFVALUE2,REFVALUE3,REFVALUE4,REfvalue5
1   AA    china      chile    ny        null null
1,  AA    Mexico     NY       NULL       NULL
2,  BB    china      USA     chile  null

well the above data model can be done  with less tables but above is just an example as there are 
huge data so this is required

As REF3 table has some columns which are NULL so left join is required

query used is

select ref1.id, description  , t2.refvalue,t22.refvalue,t222.refvalue,t2222.refvalue,t22222.refvalue

from table1 join ref1 on table1.id= ref1.id
join ref3 on ref1.refid = ref3.refid
join ref2  t2 on ref3.refcode1  = t2.refcode 
left join ref3 ref33 on ref1.refid = ref33.refid
left join ref2  t22 on ref33.refcode2  = t22.refcode
-- used left join here as can have null values
left join ref3 ref333 on ref1.refid = ref333.refid
left join ref2  t222 on ref333.refcode3  = t222.refcode
left join ref3 ref3333 on ref1.refid = ref3333.refid
left join ref2  t2222 on ref3333.refcode4  = t2222.refcode
left join ref3 ref33333 on ref1.refid = ref33333.refid
left join ref2  t22222 on ref33333.refcode5  = t22222.refcode



any suggestion for alternate logic to left join or to improve performance

cheers

• Edited by JOHN-ugrai Thursday, September 03, 2015 11:23 AM

Following can be used to create the schema



 

create table table1
( ID INTEGER,
 DESCRIPTION VARCHAR(MAX)
 
 )

 insert into table1 (id,description) values
 (1, 'AA'      ),
 (2, 'BB'      )

create table REF1
(ID INTEGER,
REFID INTEGER,
REFNO INTEGER)

insert into REF1 (ID ,  REFID, REFNO )
values
(1  ,      1,      393),
(1  ,      2,      600),
(2  ,      3,      400)


 
create table ref2
( REFCODE INTEGER,
 REFVALUE  VARCHAR(MAX)
 )
 insert into REF2 (REFCODE,REFVALUE) values
 (20, 'china'      ),
 (31, 'NY'       ),
  (45, 'USA'      ),
  (48, 'Mexico'      ),
  (60, 'chile'      )



create table REF3
(REFID INTEGER,
REFCODE1 INTEGER,
REFCODE2 INTEGER,
REFCODE3 INTEGER,
REFCODE4 INTEGER,
REFCODE5 INTEGER)

insert into REF3 (REFID ,  REFCODE1, REFCODE2, REFCODE3, REFCODE4, REFCODE5 )
values
(1  ,      20  ,      60  ,     45 ,    NULL ,   NULL),
(2 ,       48 ,       45  ,     NULL,   NULL ,    NULL),
(3 ,       20  ,      31  ,     60  ,   NULL   , NULL)

select ref1.id, description  , t2.refvalue,t22.refvalue,t222.refvalue,t2222.refvalue,t22222.refvalue

from table1 join ref1 on table1.id= ref1.id
join ref3 on ref1.refid = ref3.refid
join ref2  t2 on ref3.refcode1  = t2.refcode 
left join ref3 ref33 on ref1.refid = ref33.refid
left join ref2  t22 on ref33.refcode2  = t22.refcode
-- used left join here as can have null values
left join ref3 ref333 on ref1.refid = ref333.refid
left join ref2  t222 on ref333.refcode3  = t222.refcode
left join ref3 ref3333 on ref1.refid = ref3333.refid
left join ref2  t2222 on ref3333.refcode4  = t2222.refcode
left join ref3 ref33333 on ref1.refid = ref33333.refid
left join ref2  t22222 on ref33333.refcode5  = t22222.refcode

The desired output is

 ID,DESC, REFVALUE1,REFVALUE2,REFVALUE3,REFVALUE4,REfvalue5
1  AA china chile ny null null
1, AA Mexico NY NULL NULL
2,BB china USA chile null

• Edited by JOHN-ugrai Thursday, September 03, 2015 11:22 AM

any suggestions  ??

Hi John,

You can refer to below sample.

SELECT T1.ID,T1.DESCRIPTION,
(SELECT REFVALUE FROM REF2 WHERE REFCODE=R3.REFCODE1) REFCODE1,
(SELECT REFVALUE FROM REF2 WHERE REFCODE=R3.REFCODE2) REFCODE2,
(SELECT REFVALUE FROM REF2 WHERE REFCODE=R3.REFCODE3) REFCODE3,
(SELECT REFVALUE FROM REF2 WHERE REFCODE=R3.REFCODE4) REFCODE4,
(SELECT REFVALUE FROM REF2 WHERE REFCODE=R3.REFCODE5) REFCODE5
 FROM TABLE1 T1 JOIN REF1 R1 
							ON T1.ID=R1.ID
						JOIN REF3 R3 
							ON R1.REFID=R3.REFID

If the performance is still concerned, you may have to re-schema the table REF3 as below and pivot the result as expected.

create table REF3
(REFID INTEGER,
REFCODE INTEGER); 

insert into REF3 (REFID ,  REFCODE)
values
(1,      20),
(1,	 60),
(1,      45), 
(2,      48),
(2,      45), 
(3,      20),
(3,      31),
(3,      60);

If you have any question, feel free to let me

many thanks for this. 

This is working ..another question  like my query returns around 2 billions rows and is part of a view. Is it prudent to creat an index on ane column as data is still increasing fast

what are the implications like db issues etc or impacts on creating an index on such cases[r overall impact  , we can ignore the query performance  but what can be other implications like on db or what considerations should be there for creating an index in such case

hey Eric, I can modify the REF3 Could u suggest me the sample query for that i can pivot the rows into one column based on the REFID of REF3 , but i still need to have a join for each row value , each refcode to get refvalue so it will defeat the purpose please guide

hi Eric any suggestions on the above queries