从table1，table2多列左连接

I have two tables: table1,table2 as given below

我有两个表:table1,table2如下所示

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 output is

我的预期输出是

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

can some one help me to find out the exact query.That should use left outer join.

有人可以帮我找出确切的查询。应该使用左外连接。

3 个解决方案

#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

#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 for a running demo.

单击下面的链接以获取正在运行的演示。

SQLFiddle

#3

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

从受试者s,受试者s1,受试者s2,受试者s3,time_t t中选择t.pr,s.name,s1.name,s2.name,s3.name,其中s.id = t.pr和s1.id = t。 p2和s2.id = t.p3和s3.id = t.p4;

#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

#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