[SOLVED] Counting data using 3 tables (table 1 & 2 and 2 & 3 have ID in common)

[kuma]

I'm trying to count the number of products in each genre where the genre's parent id equals 0 for each shop. I have 3 tables: products, genres, and shops.

 

A product has a shop id and a genre id. A store has a store id. A genre has a genre id and parent id.

 

What I would like:

+----------+----------+---------+

| Shop ID | Genre ID | Count(*) |

+----------+----------+---------+

|        1 |        1 |      3 |

|        1 |        2 |      5 |

|        1 |        3 |      0 |

|        2 |        1 |      1 |

|        2 |        2 |      0 |

|        2 |        3 |      3 |

Etc...

+----------+----------+---------+

 

My attemp:

SELECT products.genre_id, COUNT(*), shops.id as 'Shop ID' FROM products LEFT JOIN genres ON (genres.genre_id = products.genre_id) LEFT JOIN shops ON (products.shop_id = shops.id) WHERE genres.parent_id = '0' GROUP BY shops.id;

 

Which produces:

+----------+----------+------+

| genre_id | COUNT(*) | Shop ID  |

+----------+----------+------+

|        1    |        2    |    1 |

|        1    |        2    |    2 |

|        3    |        3    |    3 |

+----------+----------+------+

 

which is wrong. For example shop 1 has products in more than just genre 1... Any help would be great, thank you!

 

The 3 tables:

Products

+----------+--------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+--------------+------+-----+---------+-------+

| id      | int(11)      | NO  | PRI |        |      |

| shop_id  | int(11)      | NO  |    |        |      |

| genre_id | int(11)      | NO  |    |        |      |

| title    | varchar(255) | NO  |    |        |      |

+----------+--------------+------+-----+---------+-------+

 

Genres

+-----------+--------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+-----------+--------------+------+-----+---------+-------+

| genre_id  | int(11)      | NO  | PRI |        |      |

| parent_id | int(11)      | NO  |    | 0      |      |

| name      | varchar(100) | NO  |    |        |      |

+-----------+--------------+------+-----+---------+-------+

 

Shops

+-------+--------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+--------------+------+-----+---------+----------------+

| id    | int(11)      | NO  | PRI | NULL    | auto_increment |

| name  | varchar(100) | NO  |    |        |                |

+-------+--------------+------+-----+---------+----------------+

 

MySQL Server version: 5.0.37

[fenway]

You need "GROUP BY shops.id, genre.id".

[kuma]

That works, thank you!

 

Out of curiosity, is there anywhere to display a count of zero, so it always displays all genres?

 

Like this:

+----------+----------+---------+

| Shop ID | Genre ID | Count(*) |

+----------+----------+---------+

|        1 |        1 |      3 |

|        1 |        2 |      5 |

|        1 |        3 |      0 |

|        2 |        1 |      1 |

|        2 |        2 |      0 |

|        2 |        3 |      3 |

Etc...

+----------+----------+---------+

[fenway]

It will display zero counts for any genres that are in the table... it doesn't know what "all" means... you could work around this, by why?

[kuma]

It's not really needed, just curious how to manipulate the results.

 

Thanks for your help!