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
Try this:
select *
from students s
where exists (
select 1
from subjects j
where s.student_id = j.student_id
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With