how to do this Query in Joomla?

Question

How to do this query in Joomla ?

Select student_id, ( select count(Subj_Code) 
from grade b 
where a.grade=b.grade and a.student_id=b.student_id) as subjectcount 
from grade a where a.student_id='$student_id';

Answer 1

If you are looking for subquery then you need to create a new object for it. below is the example-

$subQuery = $db->getQuery(true);
$subQuery->select('count(Subj_Code)');
$subQuery->from($db->quoteName('grade') . ' as b');
$subQuery->where('a.grade=b.grade and a.student_id=b.student_id');
$query = $db->getQuery(true);
$query->select('student_id');
$query->select('(' . $subQuery . ') as subjectcount');
$query->from($db->quoteName('grade') . ' as a');
$query->where('a.student_id = ' . $db->quote($student_id));