I have two tables in my mySQL database
[article_categories c] and [articles a]
I am using the c.cat_id to as the category identifier for a.article_cat. I have a field: a.approved with values of 1/0 to suggest approved or unapproved categories. I need one SQL statement which will return all categories and show how many are approved and how many are unapproved. So far I can get only one but not both witht he following SQL.
SELECT cat_id, cat_title, COUNT(a1.id) as approved
FROM article_categories as c
LEFT OUTER JOIN articles as a1 ON (a1.article_cat = c.cat_id AND a1.approved = 1)
GROUP BY cat_id, cat_title
ORDER BY cat_title
The above SQL shows approved categories. and if I want to get unapproved I just change a1.approved = 0. Now I need to show both approved values and unapproved values in the same row something like this:
cat_id,cat_title,approved,unapproved
25,Antichrist,1,0
1,Baptism,2,2
2,Christian Education,0,0
3,Dating,0,0
4,Family Life,12,12
5,Health,0,0
6,Homosexuality,0,0
7,Jesus,0,0
16,Last Day's Events,0,0
9,Marriage,2,0
8,Music,0,0
10,Real Life Stories,0,0
11,Religion,0,0
12,Satan,0,0
26,Second Coming,1,0
13,Sermons,0,0
27,Sexuality,0,0
24,Signs of the Times,0,0
14,Testimonies,0,0
15,Youth Issues,0,0
Can somebody kindly help me please. I spent the whole day on it yesterday but can't find a way possible.
