php mysql use subquery result in WHERE clause in the same query

Question

is it possible to use a subquery result as a criteria in WHERE clause in the same query. i have this sql code. i want to compare the ASSESSEDINCLASS which is a result in the subquery and C.SLOTS to determine it the slot is full or not. is this even possible in a single query? thanks in advance

        $str = "SELECT
                c.id,
                c.code AS classcode,
                section.name AS sectionname,
                subject.code,
                subject.subdesc,
                c.units,
                sched.name AS schedule,
                c.slots,
                c.dissolved,
                c.tutorial,
                c.inst,
                instructor.lname,
                instructor.fname,
                instructor.mname,
                instructor.suffix,
                (SELECT
                    Count(e.enrollno)
                    FROM
                    enrolldet AS e
                    Inner Join enroll ON e.enrollno = enroll.enrollno
                    Inner Join class ON e.class = class.id
                    WHERE
                    enroll.validated =  '1' AND
                    class.id =  c.id) as validatedinclass,
                (SELECT
                    Count(e.enrollno)
                    FROM
                    enrolldet AS e
                    Inner Join enroll ON e.enrollno = enroll.enrollno
                    Inner Join class ON e.class = class.id
                    WHERE
                    enroll.assessed = '1' AND
                    class.id =  c.id) as assessedinclass,
                (SELECT
                    q.id
                    FROM
                        merged
                        Inner Join class AS q ON merged.mothercode = q.id
                    WHERE
                        merged.mergefrom = c.id) AS mergedto_mothercode
            FROM
                class AS c
                Left Join sched ON c.sched = sched.id
                Left Join section ON c.section = section.id
                Left Join subject ON c.subject = subject.id
                Left Join instructor ON c.inst = instructor.userid
                Left Join course ON section.course = course.id
                Inner Join period ON c.period = period.id
            WHERE
                (period.id =  '".$period."' OR period.code = '".$period."')";
    if($status == 'open'){
        $str .= " AND c.slots < assessedinclass";
    }
    $str .= " ORDER BY subject.subdesc";

M Khalid Junaid · Accepted Answer · 2018-06-13 06:14:31Z

1

To answer your question yes you could by using Having clause, Make you use it after where clause

having assessedinclass = someval

Or

$str .= " HAVING c.slots < assessedinclass";

But what i prefer don't use dependent sub queries instead use join for these as sub clause to your main query somewhat like below

SELECT ...., 
       COALESCE(aic.assessedinclass, 0) AS assessedinclass, 
FROM   class AS c 
       JOINS....
       LEFT JOIN (SELECT class.id, 
                         COUNT(e.enrollno) AS assessedinclass 
                  FROM   enrolldet AS e 
                         INNER JOIN enroll 
                                 ON e.enrollno = enroll.enrollno 
                         INNER JOIN class 
                                 ON e.class = class.id 
                  WHERE  enroll.assessed = '1' 
                  GROUP  BY class.id) aic 
              ON aic.id = c.id 
WHERE  ( period.id = '".$period."' OR period.code = '".$period."' ) 
AND c.slots < COALESCE(aic.assessedinclass, 0)

edited Jun 13, 2018 at 6:14

answered Jun 13, 2018 at 6:09

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

3 Comments

Raymond Over a year ago

im trying to filter my result set by comparing c.slots is lesser than the result in my subquery with alias assessedinclass. is this even possible using a single query?

Raymond Over a year ago

im sorry khalid its my first time to encounter the COALESCE function and im trying to incorporate the code you provided. im having a hard time converting my codes to yours

Raymond Over a year ago

@M Khalid Junaid thanks man ive got it fiured out ands itw working now. thanks a bunch

Collectives™ on Stack Overflow

php mysql use subquery result in WHERE clause in the same query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related