Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CodeIgniter active record query to get rows from one table which relate to at least one row in another table

I have two tables, a students table and a student_subject table. I want to select students whose student_id appears on the student_subject table without duplicating students data -- only one row in the result set per student.

Table students

|student_id |name
|1                |John
|2                |James

Table subjects

id |subject_id | student_id | subject_name
1 | 2               |1                 |Mathematics
2 | 1               |1                 |English
3 | 3               |1                 |Biology

My code:

$this->db->select('*')
    ->from('students')
    ->where('student_id IN (select student_id from student_subject'));

I want to display only a single row for John like this:

|student_id |name
|1                |John

like image 518
Claudius Massery Avatar asked Oct 20 '25 10:10

Claudius Massery


1 Answers

Try this:

select *
from students s
where exists (
        select 1
        from subjects j
        where s.student_id = j.student_id
        )
like image 123
Gurwinder Singh Avatar answered Oct 22 '25 00:10

Gurwinder Singh



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!