MySQL query with LEFT OUTER JOIN and WHERE

I have three tables: stories, story_types, and comments

The following query retrieves all of the records in the stories table, gets their story_types, and the number of comments associated with each story:

         SELECT s.id AS id,
                s.story_date AS datet开发者_如何学Pythonime,
                s.story_content AS content,
                t.story_type_label AS type_label,
                t.story_type_slug AS type_slug,
                COUNT(c.id) AS comment_count
           FROM stories AS s
LEFT OUTER JOIN story_types AS t ON s.story_type_id = t.id
LEFT OUTER JOIN comments AS c ON s.id = c.story_id 
       GROUP BY s.id;

Now what I want to do is only retrieve a record from stories WHERE s.id = 1 (that's the primary key). I have tried the following, but it still returns all of the records:

         SELECT s.id AS id,
                s.story_date AS datetime,
                s.story_content AS content,
                t.story_type_label AS type_label,
                t.story_type_slug AS type_slug,
                COUNT(c.id) AS comment_count
           FROM stories AS s
LEFT OUTER JOIN story_types AS t ON s.story_type_id = t.id
                AND s.id = 1
LEFT OUTER JOIN comments AS c ON s.id = c.story_id 
       GROUP BY s.id;

I have also tried a WHERE clause at the end, which throws an error.

Can someone point out the correct syntax for a condition like this in this situation?

I'm using MySQL 5.1.47. Thanks.

I'm guessing you put the WHERE after the GROUP BY, which is illegal. See this reference on the SELECT syntax in MySQL. Try this:

SELECT
    s.id AS id,
    s.story_date AS datetime,
    s.story_content AS content,
    t.story_type_label AS type_label,
    t.story_type_slug AS type_slug,
    COUNT(c.id) AS comment_count
FROM
    stories AS s
    LEFT JOIN story_types AS t ON s.story_type_id = t.id
    LEFT JOIN comments AS c ON s.id = c.story_id 
WHERE
    s.id = 1
GROUP BY
    s.id;

editor's note: I reformatted the code to highlight the query structure

Following up this comment on the accepted answer:

It is not intuitive to me that this WHERE would go in the second JOIN

This is just to outline how proper code formatting enhances understanding. Here is how I usually format SQL:

SELECT 
  s.id               AS id,
  s.story_date       AS datetime,
  s.story_content    AS content,
  t.story_type_label AS type_label,
  t.story_type_slug  AS type_slug,
  COUNT(c.id)        AS comment_count
FROM 
  stories                  AS s
  LEFT JOIN story_types    AS t ON t.id = s.story_type_id 
  LEFT OUTER JOIN comments AS c ON s.id = c.story_id 
WHERE
  s.id = 1
GROUP BY
  s.id;

The WHERE is not on the second join. There is only one WHERE clause allowed in a SELECT statement, and it always is top level.

PS: Also note that in many database engines (apart from MySQL) it is illegal to use a GROUP BY clause and then selecting columns without aggregating them via functions like MIN(), MAX(), or COUNT(). IMHO this is bad style and a bad habit to get into.