CODEWITHSUNDEEP
mysqlcount
poNgz0r
poNgz0r

Reputation: 69

Count ads in categories with a specific parent

I got the following setup:
Table: ads with ads_id
Table: ads_to_categories with ads_id connected to ads
Table: categories with categories_id
Table: categories_to_parents with categories_id connected to categories
enter image description here

I want to count how many ads there are in a category. For example ads_id 0000000070 is in the following category: categories_id 0000000007

enter image description here

The thing is categories_id has multiple parent_id and i want to know how many ads there are in this category with parent_id 0000000003enter image description here I tried so many things i got the parameter $parent_id available and $categories_id available but somehow i get also results for the other parent_id's. Right now i don't have a query at all i got frustrated and deleted everything.

I want to know how many ads there are in categories_id 0000000007 with parent_id 0000000003. How do i do this?

For example i got this: 

SELECT count(*) 
FROM ads_to_categories as atc, categories_to_parents ctp 
WHERE atc.categories_id = '0000000007' 
AND atc.categories_id = ctp.categories_id 
AND ctp.parent_id = '0000000003'

This is giving me 1 result, when i change ctp.parent_id to '0000000004' i also get 1 hit, but this should be 0. Is it possible to get a right query with maybe a subquery or something, or should i extend ads_to_categories with a parent_id maybe?

Upvotes: 0

Views: 46

Answers (1)

poNgz0r
poNgz0r

Reputation: 69

It's impossible to do i figured out. I ended up with this query:

SELECT count(a.ads_id) as totalAds
FROM ads as a, ads_to_categories as atc, categories_to_parents ctp
WHERE a.ads_id = atc.ads_id
AND atc.categories_id = :categories_id
AND atc.categories_id = ctp.categories_id
AND (a.ads_status = :ads_status_active OR a.ads_status = :ads_status_reserved)
GROUP BY ctp.parent_id
HAVING ctp.parent_id = :parent_id

The thing is, when i change categories parent_id changes with me so there is always a match. I ended up with extended ads_to_categories table with parent_id

Upvotes: 0

Related Questions