Several columns on the left are associated with table 1, table2

Question

I have two tables: table1, table2 as below

table1: id name 1 tamil 2 english 3 maths 4 science table2: p1 p2 p3 p4 stat name 1 2 4 3 A raja 2 3 4 1 A maha 

my expected result

 p1 p2 p3 p4 tamil english science maths english maths science tamil 

can someone help me find out the exact request. This should use the left outer join.

Answer 1

 select P1, t1.Name,P2, t3.Name, P3,t4.Name, P4 , t5.Name From Table2 T2 left join table1 t1 on T2.P1 = T1.Id left join table1 t3 on T2.P2 = T3.Id left join table1 t4 on T2.P3 = T4.Id left join table1 t5 on T2.P4 = T5.Id 

Answer 2

 SELECT t1.name AS p1, t2.name AS p2, t3.name AS p3, t4.name AS p4 FROM table2 tbl2 INNER JOIN table1 t1 ON tbl2.p1 = t1.id INNER JOIN table1 t2 ON tbl2.p2 = t2.id INNER JOIN table1 t3 ON tbl2.p3 = t3.id INNER JOIN table1 t4 ON tbl2.p4 = t4.id 

Click the link below to launch the demo version.

SQLFiddle

Answer 3

select t.pr, s.name, s1.name, s2.name, s3.name from objects s, subjects s1, subjects s2, objects s3, time_t t, where s.id = t.pr and s1.id = t .p2 and s2.id = t.p3 and s3.id = t.p4;