MySQL - multiple join relation and count rows

Question

I want to build SQL query with good performance approach. I need to select all categories and count bands associated with this categories.

Structure:

bands:

 id       | band_name      |cat_id
----------|----------------|------------
 1        | Business 1     | 1  
 2        | Business 12    | 2
 3        | Business 123   | 2

categories:

id        | name           
----------|----------------
 1        | Business 1     
 2        | Business 12    
 3        | Business 123

connections:

id        | band_id        |cat_id
----------|----------------|------------
 1        | 1              | 1  
 2        | 2              | 1
 3        | 2              | 1

Query:

SELECT c.name AS category_name, (SELECT COUNT(icc.band_id) FROM cms_bands_categories_connections AS icc RIGHT JOIN cms_bands AS ib ON icc.band_id = ib.id) AS bands_sum FROM 
                    cms_bands_categories AS c 
                 JOIN cms_bands_categories_connections AS cc ON 
                    c.id = cc.category_id 
                GROUP BY
                    c.id

Results are very strange - bands_sum is evual to 3 in each row. Don't blame my structure - I had to modyfi it becouse of requirements specification change.

Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. — Taryn
– Taryn, Commented Aug 22, 2013 at 20:39

Gordon Linoff · Accepted Answer · 2013-08-22 20:38:14Z

1

Why are you using a subquery instead of just an aggregation?

SELECT c.name AS category_name, COUNT(cc.band_id) AS bands_sum
FROM cms_bands_categories c JOIN
     cms_bands_categories_connections cc
     ON c.id = cc.category_id 
GROUP BY c.id;

You don't even need the bands table to count them. That information is in the connections table.

answered Aug 22, 2013 at 20:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - multiple join relation and count rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related