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]

Re: Short query question (Where clause ..)

frizzle wrote:[color=blue]
> 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.
>[/color]

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]

Re: Short query question (Where clause ..)

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]

Re: Short query question (Where clause ..)

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

Greetings Frizzle.

[expertware@libero.it]

Re: Short query question (Where clause ..)

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/