Multiple column left join from table1, table2

Question

I have two tables: table1,table2 as given 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 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.

Possible duplicate of [How to do an INNER JOIN on multiple columns](http://stackoverflow.com/questions/2366780/how-to-do-an-inner-join-on-multiple-columns) — Muhammad Muazzam, Nov 30 '15 at 09:33
1. See normalisation. A database table is not a spreadsheet. — Strawberry, Nov 30 '15 at 09:54
@Strawberry: what you said is correct. But this was the existing DB in my project. I just working on it that's all. — Vigneswaran S, Nov 30 '15 at 10:01
@Strawberry. what do you mean ?If I had a penny... this is not the place to tease or to play with others — Vigneswaran S, Nov 30 '15 at 10:37
it's part of a phrase used when a frequently cited excuse is given as a reason for not doing something properly — Strawberry, Nov 30 '15 at 10:38
@Strawberry that you can say it openly like this. please don't teats others if you are a genius. — Vigneswaran S, Nov 30 '15 at 10:43
I'm not a genius, so I think I'm exempt from such prohibition — Strawberry, Nov 30 '15 at 10:49

Tim Biegeleisen · Answer 1 · 2015-11-30T09:34:20.150

3

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

edited Nov 30 '15 at 09:34

answered Nov 30 '15 at 09:25

Tim Biegeleisen

502,043
27
286
360

score 3 · Accepted Answer · edited Nov 30 '15 at 09:28

3

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

edited Nov 30 '15 at 09:28

Pathik Vejani

4,263
8
57
98

answered Nov 30 '15 at 09:27

CMadhu

346
1
8

score 0 · Answer 3 · edited Oct 19 '20 at 15:14

0

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

edited Oct 19 '20 at 15:14

Asclepius

57,944
17
167
143

answered Dec 03 '15 at 16:40

Preethi Raju

136
5

1

It is not apparent which type of `JOIN` this will result in. – Asclepius Oct 19 '20 at 15:15
2

Code only answers are discouraged. Explanations add clarity, insight, and provide opportunities to learn, so future visitors can solve their own related issues. – SherylHohman Oct 20 '20 at 00:35

Multiple column left join from table1, table2

3 Answers3