Short query question (Where clause ..)

[frizzle]

Hi there,
I can not seem to figure out where
to put my WHERE clause in the next query:

SELECT s.id,
s.kind_id,
s.active,
s.name,
COUNT(p.id) AS 'quantity'
FROM pr_series s
LEFT JOIN pr_products p
ON p.series_id=s.i d
GROUP BY s.id, s.name
WHERE s.kind_id = '1'
ORDER BY s.name ASC
LIMIT 0, 20

This gives me a mysql error, and i can't find out where to put "WHERE
s.kind_id = '1'". I'm new to the LEFT JOIN & GROUP BY things, i've read
about it but, as i said, can't get it to work ... :(
Are the WHERE and JOIN / GROUP BY parts not meant to be in 1 query?

Any help would be greatly appreciated ...

Greetings Frizzle.

[Carl]

frizzle wrote:

Hi there,
I can not seem to figure out where
to put my WHERE clause in the next query:

SELECT s.id,
s.kind_id,
s.active,
s.name,
COUNT(p.id) AS 'quantity'
FROM pr_series s
LEFT JOIN pr_products p
ON p.series_id=s.i d
GROUP BY s.id, s.name
WHERE s.kind_id = '1'
ORDER BY s.name ASC
LIMIT 0, 20

This gives me a mysql error, and i can't find out where to put "WHERE
s.kind_id = '1'". I'm new to the LEFT JOIN & GROUP BY things, i've read
about it but, as i said, can't get it to work ... :(
Are the WHERE and JOIN / GROUP BY parts not meant to be in 1 query?

Any help would be greatly appreciated ...

Greetings Frizzle.

Examples are shown all throughout the mysql manual if thats what you are
using. Try looking at the JOIN SYTAX page for more details (
http://dev.mysql.com/doc/mysql/en/join.html ).

Carl.

P.S. The WHERE should be before the GROUP BY clause.

[ZeldorBlat]

Switch the GROUP BY and the WHERE:

SELECT s.id,
s.kind_id,
s.active,
s.name,
COUNT(p.id) AS 'quantity'
FROM pr_series s
LEFT JOIN pr_products p
ON p.series_id=s.i d
WHERE s.kind_id = '1'
GROUP BY s.id, s.name
ORDER BY s.name ASC
LIMIT 0, 20

[frizzle]

Thank you * both * sooooo incredibly much!!!!!!!
You made my night :)

Greetings Frizzle.

[expertware]

Sweet dreams... And for the following nights you might
take a look at this tool for automated query creation:
http://151.100.3.84/technicalpreview/