Im trying to do a select statement that allows me to get the count of ads per category where showstatus is equal to 1
one table is tbl_ads and the other is tbl_ads_to_categories
in the tbl_ads has the ad_id and also the show_status
in the tbl_ads_to_categories, it has the ads_id and the category_id.
I believe I have to use count(ads_id) somewhere and GROUP BY categories_id and a JOIN cos there are two different tables with the same column.
both tables tbl_ads and tbls_ads_to_categories have the same column 'ads_id'
I tried using this statement
$query = "SELECT *, COUNT(ads_id) FROM tbl_ads_to_categories WHERE tbl_ads_to_categories.ads_id = tbl_jobs.ads_id AND show_status = '1' GROUP BY categories_id";
I get an error saying
Unknown column 'tbl_ads.ads_id' in 'where clause'