Mysql count child row tables 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

I want all users to count how many times they have orders with the flag 'fA'

RESULTS WHICH NEED:

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

I'm trying to:

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 request excludes user = u2 because it has no order with the fA flag ; I need user = u2 with orders = 0

Maybe something like this:

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 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

'ON' "LEFT JOIN", :

SELECT u.user_id, COUNT(o.user_id)
FROM user u
LEFT JOIN `order` o ON u.user_id = o.user_id AND o.flag = 'fA'
GROUP BY user_id

SQLFiddle

Answer 3

CASE. MYSQL, , :

  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 4

:

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;

Answer 5

:

1. .

   SELECT u.user_id, (SELECT count(user_id) FROM 'order' o WHERE o.user_id = u.user_id AND o.flag = 'fA') count
   FROM 'user' u;
   

2. LEFT JOIN :

   SELECT u.user_id, count(o.user_id) count
   FROM 'user' u
   LEFT JOIN (SELECT user_id FROM 'order' WHERE flag = 'fA') o USING (user_id)
   GROUP BY u.user_id;
   

3. LEFT JOIN :

   SELECT u.user_id, count(o.user_id) count
   FROM 'user' u
   LEFT JOIN 'order' o ON o.user_id = u.user_id AND o.flag = 'fA'
   GROUP BY u.user_id;
   

, - , .

SQL Fiddle