仅选择必须存在于两个表上并仅返回单行的数据

i have two tables, students table and student_subject. i want to pick students whose student_id appeared on student_subject table, and i want to return only single row.

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 single row like this:-
|student_id |name

|1                |John

Try this:

select *
from students s
where exists (
        select 1
        from subjects j
        where s.student_id = j.student_id
        )

You need to use query as below:

$this->db->select('students.student_id, students.name')
    ->from('students')
    ->join('subjects', 'students.id = subjects.student_id')
    ->group_by('students.id');

try this.using join

$this->db->select('students.student_id as sid,students.name as sname',false);
$this->db->from('students s1');
$this->db->join('subjects s2','s1.student_id = s2.student_id');
$this->db->groupby('s1.student_id');

access using alias name like

$row->sid,$row->sname

note:- group_by() was formerly known as groupby(), which has been removed.