mysql count child table rows with condition

I have two tables: "user" -> "order"

TABLE: user

user_id
-----------
    u1
    u2

TABLE: order

order_id | user_id | flag
-------------------------
    o1   |    u1   |  fA
    o2   |    u2   |  fB

Y need obtain all users counting how many times have orders with flag 'fA'

RESULTS WHAT I NEED:

user_id | orders
----------------
   u1   |   1
   u2   |   0

I TRY:

SELECT
    u.user_id,
    COUNT(o.order_id) AS orders
FROM
    `user` AS u LEFT JOIN
    `order` AS o USING (user_id)
WHERE
    o.flag IS NULL OR
    o.flag IN ('fA')
GROUP BY
    u.user_id;

But, this query is excluding user=u2 because he don't have an order with flag fA; I need the user=u2 appear with orders=0

Maybe something like that:

SELECT
    u.user_id,
    COUNT(o.order_id IF o.flag IN('fA')) OR 0 AS count ...

Tables and data:

CREATE TABLE `user` (user_id VARCHAR(2) NULL);
CREATE TABLE `order` (order_id VARCHAR(2) NULL,user_id VARCHAR(2) NULL,flag VARCHAR(2) NULL);
INSERT INTO `user` VALUES ('u1'), ('u2');
INSERT INTO `order` VALUES ('o1','u1','fA'),('o2','u2','fB');

From stackoverflow

• You need to use a left outer join instead of left join

  SELECT
      u.user_id,
      COUNT(o.order_id) AS orders
  FROM
      `user` AS u LEFT OUTER JOIN
      `order` AS o USING (user_id)
  WHERE
      o.flag IS NULL OR
      o.flag IN ('fA')
  GROUP BY
      u.user_id;
  

• You can try using a CASE statement. I don't have MYSQL here today so I can validate the syntax, but it should be close to this:

    SELECT   u.user_id,
               CASE
                  WHEN (SELECT COUNT (*)
                          FROM ORDER z
                         WHERE z.user_id = USER.user_id) > 0
                     THEN COUNT (*)
                  ELSE 0
               END CASE AS cnt
          FROM USER
      GROUP BY USER.user_id;
  

• This query will work:

  SELECT
      u.user_id,
      COUNT(o.order_id) AS orders
  FROM
      `user` AS u LEFT OUTER JOIN
      (SELECT user_id, order_id FROM `order` WHERE flag IS NULL OR flag IN ('fA')) as o
      USING (user_id)
  GROUP BY
      u.user_id;
  

  Nadia Alramli : I'm using LEFT OUTER JOIN in case there is a user that has no entries in the order table yet. Is there a reason why you don't prefer a sub-query solution?

  Nadia Alramli : I'm glad the solution works. About outer of inner join you can refer to the documentation for more details http://dev.mysql.com/doc/refman/5.0/en/join.html