Mysql in conditon related query

Question

SELECT ac1.id, ac1.course_name, case when a.yettoapprove is not null 
 then 'true'
 else 'false'
 end OrderedAll , a.* from (SELECT ac.id, sum(case when ad.status = '2' then 1 else 0 end) as yettoapprove , sum(case when ad.status = '1' then 1 else 0 end) as approved,case when ad.status = '2' is not null 
 then 'true'
 else 'false'
 end OrderedAll FROM aw_ou_student_data ad , jos_users ju , aw_ou_lookup_courses ac,aw_ou_lookup_colleges ac1 where ac1.id=ju.college_code and ac.id in (27,28,29,30,133,32,33,34,35,36,37,38,39,40,41,42,43,44,134,135) and ac.school_id=2 and ju.id=ad.user_id and ac.id=ju.course_code GROUP BY ac.id ORDER BY ac.id ) a left join  aw_ou_lookup_courses ac1 on ac1.id = a.id

In the above inner query in above one i have given 20 ac.id but i am getting only 14 out records as a result. How do i get the total 20 records as a result. i.e The id present in the in condition if it doesnt satisfy then i should a record with 0 as values for it beside that id.

How do i do it..?

Answer 1

A simple left join with condition should do it:

SELECT
    table1.id,
    table2.*
FROM
    table table1
    LEFT JOIN table2 ON table1.id = table2.id AND (your conditions here)