I'm working my way through the php world but still finding my feet. I've written a query that works nicely for collecting what i need from mysql, but I just cannot get it to work with a WHERE condition. Any help would be much appreciated!
Here's my working query without the WHERE condition:
SELECT foo.*, users.name, SUM(bar.amt) Total FROM foo
LEFT JOIN bar ON foo.id = bar.foo_id
LEFT JOIN users ON foo.foo_owner_id = users.id
LIMIT 16 GROUP BY foo.id"
And here's what I think should work to add the WHERE condition but doesn't work...
SELECT foo.*, users.name, SUM(bar.amt) Total FROM foo
WHERE foo.category = $var LEFT JOIN bar ON foo.id = bar.foo_id
LEFT JOIN users ON foo.foo_owner_id = users.id LIMIT 16 GROUP BY foo.id"
Any and all suggestions are welcome, thanks!!
Quite simple: all the WHERE conditions should be placed after all the JOIN's.
You need to put the WHERE clause after all the joined tables. Hence
SELECT foo.*, users.name, SUM(bar.amt) Total
FROM foo
LEFT JOIN bar ON foo.id = bar.foo_id
LEFT JOIN users ON foo.foo_owner_id = users.id
WHERE foo.category = $var
GROUP BY foo.id
LIMIT 16;
You probably also want the LIMIT clause to be last.
use
SELECT
foo.*,
users.name,
SUM(bar.amt) Total
FROM foo
LEFT JOIN bar ON foo.id = bar.foo_id
LEFT JOIN users ON foo.foo_owner_id = users.id
WHERE foo.category = $var
GROUP BY foo.id
LIMIT 16
when you are using left or right join then always use where clause after joins.
SELECT foo.*, users.name, SUM(bar.amt) Total
FROM foo
LEFT JOIN bar ON foo.id = bar.foo_id
LEFT JOIN users ON foo.foo_owner_id = users.id
WHERE foo.category = $var
GROUP BY foo.id
LIMIT 16;
even if u want order by id etc use it before LIMIT