join - SQL joining 4 tables issue -

i have 4 tables:

t1

id   id1   title 1    100   title1 2    100   title2 3    100   title3 

t2

id  text 1   long1 2   long2 

t3

id1  id2 100  200 

t4

  id4  id2   subject    1    200       2    200    b    3    200    c    4    200    d    5    200    e 

i want output in result format:

title    text   subject  title1   long1    title2   long2    b title3   null     c null     null     d null     null     e 

so made query gives me more results should be.on example titles asre displayed more times once etc.

select      t1.title,     t2.text,     t4.subject t1     left outer join t2 on t1.id=t2.id     inner join t3      on t1.id1=t3.id1     left outer join t4 on t4.id2=t3.id2      t1.id1=100 

thanks help

disclaimer: don't work db2. after browsing through documentation have found db2 supports row_number() , full outer join, might wrong.

to rid of n:m relationship 1 has build additional key. in case simple solution add row number each record in t1 , t4 , use join condition. row_number that, produces numbers groups of data defined partition by in ascending sequence in order defined order by.

as there difference in number of records in t1 , t4, , unknown 1 has more records, use full outer join join them.

you can see the test (sql server version) @ sql fiddle.

select t1_rn.title,        t2.[text],        t4_rn.subject (   select t1.id,          t1.title,          t1.id1,          t3.id2,          row_number() over(partition t1.id1                            order id) rn     t1    inner join t3       on t1.id1 = t3.id1 ) t1_rn full outer join (   select t4.subject,          t3.id1,          t4.id2,          row_number() over(partition t4.id2                             order id4) rn     t4    inner join t3       on t4.id2 = t3.id2 ) t4_rn   on t1_rn.id1 = t4_rn.id1  , t1_rn.id2 = t4_rn.id2  , t1_rn.rn = t4_rn.rn left join t2   on t1_rn.id = t2.id 

this kind of work should done on presentation side of application, believe software using requires prepared data.