mysql count child table rows with condition

Question

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');

Answer 1

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;

Answer 2

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;

Answer 3

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;