I have an SQL Query (MySQL) which uses a combination of LEFT JOIN, GROUP BUY, ORDER BY, and LIMIT. When I remove LEFT JOIN and GROUP BY it works fine. What is the correct way to add in my additional LEFT JOIN and GROUP logic to make this work?
x
SELECT tbl_headlines.*, count(tbl_weekly_builds.id) as related_count
FROM tbl_headlines
LEFT JOIN tbl_weekly_builds ON tbl_headlines.id = tbl_weekly_builds.headline
GROUP BY tbl_headlines.id
WHERE tbl_headlines.deleted is null
ORDER BY LOWER(tbl_headlines.label) asc
LIMIT 0, 100;
Advertisement
Answer
Your syntax is incorrect – where clause
will be before group by
SELECT tbl_headlines.id, count(tbl_weekly_builds.id) as related_count
FROM tbl_headlines
LEFT JOIN tbl_weekly_builds ON tbl_headlines.id = tbl_weekly_builds.headline
WHERE tbl_headlines.deleted is null
GROUP BY tbl_headlines.id
ORDER BY LOWER(tbl_headlines.label) asc
LIMIT 0, 100;