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?

@raheelshan here is the table structure, my apology for the late reply. http://sqlfiddle.com/#!2/33275 — Ibrahim Azhar Armar, Sep 14 '12 at 05:54
sorry @Ibrahim Azhar Armar but can you please provide some data too — Muhammad Raheel, Sep 14 '12 at 05:57
@raheelshan sure, i am sorry for not adding any data. going to do it now. — Ibrahim Azhar Armar, Sep 14 '12 at 06:04
@raheelshan added sample data, here is the link http://sqlfiddle.com/#!2/5adaa , took me quite some time, since i have some ten thousand records and extra columns which i had to edit and update. — Ibrahim Azhar Armar, Sep 14 '12 at 06:29

MvG · Accepted Answer · 2012-09-14T07:07:00.167

2

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

edited Sep 14 '12 at 07:07

answered Sep 14 '12 at 05:52

MvG

57,380
22
148
276

thank you :), here is the link to fiddle.http://sqlfiddle.com/#!2/33275 , i am going to test this query and get back to you. – Ibrahim Azhar Armar Sep 14 '12 at 05:54
i am updating the fiddle with data now. however your old query was not working, let ne test the new onse. – Ibrahim Azhar Armar Sep 14 '12 at 06:08
this query seems to work. thank you so much, if you want to test with sample data, i have updated the fiddle with data. here is the link http://sqlfiddle.com/#!2/5adaa – Ibrahim Azhar Armar Sep 14 '12 at 06:30
this query gives me what i want. is there any other way out without using sub select or sub queries? – Ibrahim Azhar Armar Sep 14 '12 at 06:58
@IbrahimAzharArmar: There is no sub select or sub query involved in either of my answers, just a suitable grouping of join factors. But perhaps you'll prefer my latest version, without parentheses in the join. – MvG Sep 14 '12 at 07:10
this query looks so great, but damn doctrine does not support right join. only inner join and left join are supported. is it possible to make a query using only left join and inner join? (i wish i could vote you more number of times, as a token of appreciation i upvoted your answered question which i found useful :)). – Ibrahim Azhar Armar Sep 14 '12 at 07:29
@IbrahimAzharArmar, can't think of a way, but perhaps others can. I don't know doctrine either, but I have added suitable tags to your question. After a quick glance at the docs, can you make use of [Native SQL](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/native-sql.html) for this? – MvG Sep 14 '12 at 07:45
1

@j0k: Because I think of them as two answers. Different ideas lie at the heart of each of them. Either one will get the job done. There might be reasons to prefer one over the other, and community votes on the different alternatives might be used to reflect those preferences. The way I read http://meta.stackexchange.com/q/25209, this practice is OK, even though others might have chosen differently. – MvG Sep 14 '12 at 08:14

score 2 · Answer 2 · answered Sep 14 '12 at 06:37

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.

not able to get what is happening in the query. i have several cities in my website, and i need to display the subcategory list with business count according to the city. so where do i tell mysql to count the records of particular city only? — Ibrahim Azhar Armar, Sep 14 '12 at 06:43
The `t4.id = 1` check is there, just the way you wrote it in that original query of yours. Of course, you could drop `t4` altogether and select on `t3.city_id = 1` instead. — MvG, Sep 14 '12 at 06:55
oh ok. thank you very much. is there any way i could go without using sub selects or sub query. (actually i am using Doctrine Query Language). thank you very much for the help though, i really appreciate it. — Ibrahim Azhar Armar, Sep 14 '12 at 07:03

score 1 · Answer 3 · answered Sep 14 '12 at 06:48

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

answered Sep 14 '12 at 06:48

Muhammad Raheel

19,823
7
67
103

it returns empty set, whereas record exist, i want mysql to fetch records if even city does not exist, the only thing it should take into consideration is while counting the business. – Ibrahim Azhar Armar Sep 14 '12 at 06:55
1

You're not grouping by subcategories. And your use of an `inner join` will loose all the `NULL` values from the preceding `left join`s, which means you'll be loosing all the rows with a zero count. – MvG Sep 14 '12 at 06:59
use left join in the derieved query at both places inside the query and outside the query so that it will work ok. use ifnull(column name,0) if you want 0 instead of null value – Muhammad Raheel Sep 14 '12 at 07:42

select query and count based on condition

3 Answers3

Linked