codeigniter join with same columns [duplicate]

Question

I am working on Educational project where i came across simple logic. I have two table month and semester_type. bellow are their schema and data;

month table

month_id    month_name  month_value lupdate
1           January     1   
2           February    2   
3           March       3   
4           April       4   
5           May         5   
6           June        6   
7           July        7   
8           August      8   
9           September   9   
10          October     10  
11          November    11  
12          December    12  

here is my semester_type table;

semester_type_id    semester_type_name  start_month end_month
1                   Fall                8           12
2                   Summer              1           4

and here is the output i want;

Semester Name   Start Month End Month
Fall            August      December
Summer          January     April

i am confused with inner joining the month_id with start_month and end_month columns in both tables. can someone help me with codeigniter query

Answer 1

Join month's table twice with your semester table

select s.semester_type_name,
m.month_name start_month ,
m1.month_name end_month
from semester s
join month m on(m.month_id = s.start_month)
join month m1 on(m1.month_id = s.end_month)

Demo

Using codeigniter's active record library you can write it as

$this->db->select('s.semester_type_name,m.month_name start_month ,m1.month_name end_month') 
    ->from('semester s')
    ->join('month m','m.month_id = s.start_month')
    ->join('month m1','m1.month_id = s.end_month')
    ->get()
    ->result();