if field X in any row has value Y then ignore else group results by GROUP BY in mysql without subquery

user_10533250 0
Viewed 122

I have two tables:

users

id |  admin_id   | name
 -----------------------
  1 |    10       | a
  2 |    10       | b
  3 |    15       | c
  4 |    10       | d
  5 |    10       | e
  6 |    10       | f

status

 id |  user_id    | status
 -----------------------
  1 |     2       | error
  2 |     2       | success
  3 |     2       | error
  4 |     4       | success
  5 |     6       | error

now I am trying to get all users of admin_id 10 who don't have any entry in table status and the users which don't have any entry with status success in status table by using group by clause. My expected results are:

Result:

 id |  admin_id   | name  |  status
 ------------------------------------
  1 |     10      |   a   |  null
  5 |     10      |   e   |  null
  6 |     10      |   f   |  error

So as per the expected results the query should ignore the user_id 2,4 because these two have success status in status table. Right now I am trying to make the query like this:

SELECT
    users.*,
    status.status
FROM
    users
LEFT JOIN
    status ON users.id = status.user_id
WHERE
    users.admin_id = 10 AND status.status != "success"
GROUP BY
    users.id

It can be done by sub-query but my requirement is to do it with single query and without using sub-query.

Is there a way to do this?
Thanks!

left-join group-by mysql
2 Answers

You should move the success condition away from the where clause into the join ... on clause:

SELECT
    users.*,
    status.status
FROM
    users
LEFT JOIN
    status ON users.id = status.user_id AND status.status != "success"
WHERE
    users.admin_id = 10 
GROUP BY
    users.id

A side note unrelated to your question: it is non-standard to SQL to specify columns in your select clause which are neither grouped by, nor functionally dependent on a grouped by expression, nor aggregated.

So better is to aggregate the status in some way (choose what best suits your needs: min, max, ...):

SELECT
    users.*,
    min(status.status) as status
FROM
    users
LEFT JOIN
    status ON users.id = status.user_id AND status.status != "success"
WHERE
    users.admin_id = 10 
GROUP BY
    users.id
user_54598390

Try the next query. We first group by the users columns you are using on the SELECT clause, then we use HAVING clause for discard those groups whose count of status="success" is greater than zero.

SELECT
    users.id,
    users.admin_id,
    users.name,
    MAX(status.status)
FROM
    users
LEFT JOIN
    status ON status.user_id = users.id
WHERE
    users.admin_id = 10
GROUP BY
    users.id, users.admin_id, users.name
HAVING
    SUM(CASE WHEN status.status = "success" THEN 1 ELSE 0 END) = 0
user_103664950
Related