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

Join table twice - on two different columns of the same table and Select Join for two columns in codeigniter and How to join the same table twice and assign table aliases in Codeigniter? — mickmackusa
– mickmackusa ♦, Commented Nov 16 at 10:45

M Khalid Junaid · Accepted Answer · 2014-10-12 10:51:23Z

3

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();

answered Oct 12, 2014 at 10:51

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shariati Over a year ago

thank you @M Khalid Junaid. That actually works. but i want to get the start_month and end_month figures from semester_type table also

M Khalid Junaid Over a year ago

@ArifShariati you can do so just change aliases select s.semester_type_name,m.month_name smonth ,m1.month_name emonth,s.start_month s.end_month

Collectives™ on Stack Overflow

codeigniter join with same columns [duplicate]

1 Answer 1

`Demo`

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related