Search code examples
mysqlselectcount

MySQL - select rows where count in joined table equals 1

I need to select the rows from table "web_users" only if rows of another joined table called "web_users_branches" equals to 1.

What I have now:

SELECT id, code from web_users 
JOIN
(
SELECT client_code
  FROM web_users_branches
HAVING COUNT(*) = 1
) as t2
ON web_users.code = t2.client_code;

I get empty result.

Database example:

Web Users table:

id code
1  0001
2  0002
3  0003

Web Users Branches table:

id  client_code
1   0001
2   0001
3   0002
4   0003
5   0003

Now after this query I should get only the user which client_code is 0002, because all the other user client_code count is not equal to 1 (there is x2 0003 and x2 0001). Any ideas?

Solution

  • I think you just want a group by in the subquery:

    SELECT u.id, u.code 
FROM web_users u JOIN
     (SELECT client_code
      FROM web_users_branches
      GROUP BY client_code
      HAVING COUNT(*) = 1
     ) c
     ON u.code = c.client_code;