select query and count based on condition

Question

I want to select all categories, subcategories and count the number of business that belongs to subcategory. this is the SQl query i am using.

SELECT
    c.id, 
    c.name,
    c.slug,
    sc.id,
    sc.name,
    sc.slug,
    COUNT(bsc.id) AS business_count
FROM 
    fi_category c
LEFT JOIN 
    fi_subcategory sc ON c.id = sc.category_id AND (sc.deleted_at IS NULL) 
LEFT JOIN 
    fi_business_subcategory bsc ON sc.id = bsc.subcategory_id AND (bsc.deleted_at IS NULL) 
WHERE 
    (c.deleted_at IS NULL) 
GROUP BY 
    c.id, sc.id

however there is more i want to do, business_count should be filtered according to the city they belong i.e in the end i want to select all category, subcategory but business_count should have a clause like WHERE city.id = 1, for this i guess i have to use count as subquery which i am not been able to figure out.

below is the relationship structure from fi_business_subcategory to fi_city.

1) fi_business_subcategory

+----+----------------+-------------+
| id | subcategory_id | business_id |
+----+----------------+-------------+

2) fi_business

+----+---------+-----------+
| id | name    | suburb_id |
+----+---------+-----------+

3) fi_suburb

+-----+--------+---------+
| id  | name   | city_id |
+-----+--------+---------+

4) fi_city

+----+--------+
| id | name   |
+----+--------+

i tried something like this, but this doesn't seem to work

SELECT
    c.id, 
    c.name,
    c.slug,
    sc.id,
    sc.name,
    sc.slug,
    bsc.business_count
FROM 
    fi_category c
LEFT JOIN 
    fi_subcategory sc ON c.id = sc.category_id AND (sc.deleted_at IS NULL) 
LEFT JOIN (
    SELECT 
        COUNT(business_id) t1.business_count, t1.subcategory_id 
    FROM
        fi_business_subcategory t1
    LEFT JOIN
        fi_business t2 ON t2.id = t1.business_id
    LEFT JOIN
        fi_suburb t3 ON t3.id = t2.suburb_id
    LEFT JOIN
        fi_city t4 ON t4.id = t3.city_id
    WHERE
        t4.id = 1
    GROUP BY
        t1.subcategory_id
) bsc ON sc.id = bsc.subcategory_id AND (bsc.deleted_at IS NULL)
WHERE 
    (c.deleted_at IS NULL) 
GROUP BY 
    c.id, sc.id

how should i build up the query to achieve what i want?

Answer 1

Try this

select 
    c.id,
    c.name,
    count(sc.name) as Count
from fi_category as c
left join fi_subcategory as sc on sc.category_id = c.id
left join fi_business_subcategory as  fbs on fbs.subcategory_id = sc.id
inner join (
select 
    fb.name,
    fs.id,
    fs.city_id

from fi_business as fb 
inner join fi_suburb as fs on fs.id = fb.suburb_id
where fs.city_id = 1



) as  fb on fb.id = fbs.business_id
group by c.id

Answer 2

If you want to use a subquery, a correct way to phrase your second query with as litle change as possible would be this:

SELECT
    c.id, 
    c.name,
    c.slug,
    sc.id,
    sc.name,
    sc.slug,
    IFNULL(bsc.business_count, 0)
          -- turn NULL from left join into 0
FROM 
    fi_category c
LEFT JOIN 
    fi_subcategory sc ON c.id = sc.category_id AND (sc.deleted_at IS NULL) 
LEFT JOIN (
    SELECT 
        COUNT(*) business_count, t1.subcategory_id
          -- removed table name from alias name,
          -- and improved performance by simply counting rows
    FROM
        fi_business_subcategory t1
    LEFT JOIN
        fi_business t2 ON t2.id = t1.business_id
    LEFT JOIN
        fi_suburb t3 ON t3.id = t2.suburb_id
    LEFT JOIN
        fi_city t4 ON t4.id = t3.city_id
    WHERE
        t4.id = 1 AND (t1.deleted_at IS NULL)
          -- check deletion in subquery for performance
    GROUP BY
        t1.subcategory_id
) bsc ON sc.id = bsc.subcategory_id
          -- no longer need to check deletion here
WHERE 
    (c.deleted_at IS NULL) 
GROUP BY 
    c.id, sc.id

Fiddle here.

Answer 3

I see no reason why you should have to use a subquery. I believe that you can simply combine fi_business and fi_business_subcategory to a single parenthesized table factor.

SELECT
    c.id, 
    c.name,
    c.slug,
    sc.id,
    sc.name,
    sc.slug,
    COUNT(bsc.id) AS business_count
FROM
    fi_category c
LEFT JOIN
    fi_subcategory sc ON c.id = sc.category_id AND (sc.deleted_at IS NULL)
LEFT JOIN (
        fi_business b
    INNER JOIN
        fi_business_subcategory bsc ON b.id = bsc.business_id AND (bsc.deleted_at IS NULL)
    INNER JOIN
        fi_suburb su ON su.id = b.suburb_id AND su.city_id = 1
    ) ON sc.id = bsc.subcategory_id
WHERE 
    (c.deleted_at IS NULL) 
GROUP BY 
    c.id, sc.id

I've checked that this is valid SQL for your table structure. I guess chances are good that it will yield the desired result, even though your fiddle doesn't contain any data yet. See the manual on JOIN syntax for details on where you can use parentheses in a join.

You might also ask yourself if you really need all the joins to be left joins. Writing things using inner joins would be much easier.

As joins are executed left to right, you might do the inner joins first, followed by a sequence of right joins. This avoids the parentheses:

SELECT
    c.id cat_id,
    c.name cat_name,
    c.slug cat_slug,
    sc.id sub_id,
    sc.name sub_name,
    sc.slug sub_slug,
    COUNT(bsc.id) AS business_count
FROM
    fi_business b
INNER JOIN
    fi_business_subcategory bsc ON b.id = bsc.business_id
    AND (b.deleted_at IS NULL) AND (bsc.deleted_at IS NULL)
INNER JOIN
    fi_suburb su ON su.id = b.suburb_id AND su.city_id = 1
RIGHT JOIN
    fi_subcategory sc ON sc.id = bsc.subcategory_id
RIGHT JOIN
    fi_category c ON c.id = sc.category_id AND (sc.deleted_at IS NULL)
WHERE
    (c.deleted_at IS NULL)
GROUP BY
    c.id, sc.id