How to query the same table multiple times?-CodePudding

I have 3 table, Application, School, Student

Table School :

id	name	school_code
0	sch_1	001
1	sch_2	002
2	sch_3	003
3	sch_4	004
4	sch_5	005
5	sch_6	006
6	sch_7	007
7	sch_8	008
8	sch_9	009
9	sch_10	010
10	sch_11	011

Table Application ：

app_id	sch_choice_1	sch_choice_2	sch_choice_3	sch_choice_4	sch_choice_5	sch_choice_6	sch_choice_7	sch_choice_8	sch_choice_9	sch_choice_10	student
1	0	1	2	3	4	5	6	7	8	9	1
2	9	8	7	6	5	4	3	2	1	0	2
3	1	9	0	8	7	2	6	3	5	4	3

Table Student ：

id	name
1	student1
2	student2
3	student3

I want to select the school choices for each student with the school detail.

My ideal output is like below

app_id	student	name1	school_code1	name2	school_code2	name3	school_code3	name4	school_code4	name5	school_code5	name6	school_code6	name7	school_code7	name8	school_code8	name9	school_code9	name10	school_code10
1	1	sch_1	001	sch_2	002	sch_3	003	sch_4	004	sch_5	005	sch_6	006	sch_7	007	sch_8	008	sch_9	009	sch_10	010
2	2	sch_10	010	sch_9	009	sch_8	008	sch_7	007	sch_6	006	sch_5	005	sch_4	004	sch_3	003	sch_2	002	sch_1	001

I have tried using left join , but in order to get all the 10 choices school details I will have to left join the School table 10 times. Is there any better ways to get the same output?

SQL that currently using is like below :

select 
app.app_id,
app.student,
r1.NAME as name1,
r1.SCHOOL_CODE as school_code1,
r2.NAME as name2,
r2.SCHOOL_CODE as school_code2,
r3.NAME as name3,
r3.SCHOOL_CODE as school_code3,
r4.NAME as name4,
r4.SCHOOL_CODE as school_code4,
r5.NAME as name5,
r5.SCHOOL_CODE as school_code5,
r6.NAME as name6,
r6.SCHOOL_CODE as school_code6,
r7.NAME as name7,
r7.SCHOOL_CODE as school_code7,
r8.NAME as name8,
r8.SCHOOL_CODE as school_code8,
r9.NAME as name9,
r9.SCHOOL_CODE as school_code9,
r10.NAME as name10,
r10.SCHOOL_CODE as school_code10
from 
  T_APPLICATION app 
  left join T_SCHOOL r1 on r1.ID = app.SCH_CHOICE1 
  left join T_SCHOOL r2 on r2.ID = app.SCH_CHOICE2 
  left join T_SCHOOL r3 on r3.ID = app.SCH_CHOICE3 
  left join T_SCHOOL r4 on r4.ID = app.SCH_CHOICE4 
  left join T_SCHOOL r5 on r5.ID = app.SCH_CHOICE5 
  left join T_SCHOOL r6 on r6.ID = app.SCH_CHOICE6 
  left join T_SCHOOL r7 on r7.ID = app.SCH_CHOICE7 
  left join T_SCHOOL r8 on r8.ID = app.SCH_CHOICE8 
  left join T_SCHOOL r9 on r9.ID = app.SCH_CHOICE8 
  left join T_SCHOOL r10 on r10.ID = app.SCH_CHOICE10

CodePudding user response：

You can use subquery instead of join

select 
   app_id,
   student,
   (select name from T_SCHOOL where id=sch_choice_1) as name1,
   (select school_code from T_SCHOOL where id=sch_choice_1) as school_code1,
   (select name from T_SCHOOL where id=sch_choice_2) as name2,
   (select school_code from T_SCHOOL where id=sch_choice_2) as school_code2,
   
   ......

   (select name from T_SCHOOL where id=sch_choice_10) as name10,
   (select school_code from T_SCHOOL where id=sch_choice_10) as school_code10,  
from T_APPLICATION

However, it would be better to store each choice in one row and modify the T_APPLICATION table to (app_id, student_id, school_id, order)