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

Hi Eric
if you can suggest on the pivot query, as that will be the best solution

• Edited by JOHN-ugrai 19 hours 57 minutes ago

Hi John,

Regarding index on your large table, I don't have much experience on this point. So very generally I can say the index should be created on what columns your query is relying, usually the columns in group by, in join on condition and after where clause should be indexed. You would get benefit from index when selecting while the index might impact the insert and update performace and index consumes storage volume. In case you'd like more specific advice, you can post a new thread and the community would help.

For the pivot query, you can refer to below sample.

  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);

 
;WITH Cte AS
(
SELECT T1.ID,R1.REFID,T1.DESCRIPTION,R2.REFVALUE,ROW_NUMBER() OVER(PARTITION BY T1.ID,R1.REFID ORDER BY T1.ID) RN
 FROM TABLE1 T1 JOIN REF1 R1 
							ON T1.ID=R1.ID
						JOIN REF3 R3 
							ON R1.REFID=R3.REFID
						JOIN REF2 R2
							ON R3.REFCODE=R2.REFCODE
)
SELECT * FROM CTE C
PIVOT
(
MAX(C.REFVALUE) FOR RN IN ([1],[2],[3],[4],[5])
)PVT

If you have any question, feel free to let me