[SOLVED] (Simple?) counting question - data from 2 tables

[kuma]

I have 2 tables, genres and products. I'm trying to count the number of products in each genre where the genre's parent_id = 0. Here's my attempt:

 

SELECT products.genre_id, COUNT(*) FROM products GROUP BY genre_id, genres.genre_id FROM products, genres WHERE (genres.parent_id = '0' AND genres.genre_id = products.genre_id);

 

I get: "ERROR 1064 (42000): You have an error in your SQL syntax;"

 

Any help would be great! This seems like a simple query but I can't figure out the correct syntax.

 

MySQL server version: 5.0.37

 

Table Structure:

categories:

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

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

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

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

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

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

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

 

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  |    |        |      |

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

 

 

[fenway]

You have the group by in the wrong place... try (untested):

 

SELECT products.genre_id, COUNT(*) FROM products FROM products LEFT JOIN genres ON (genres.genre_id = products.genre_id WHERE genres.parent_id = '0' GROUP BY genres.genre_id 

[kuma]

That worked, thank you very much!

 

(There was a typo in the code. Here's what worked if anyone needs it)

SELECT products.genre_id, COUNT(*)  FROM products LEFT JOIN genres ON (genres.genre_id = products.genre_id) WHERE genres.parent_id = '0' GROUP BY genres.genre_id;

 

[fenway]

(There was a typo in the code.

Hence the "untested" ;-)