MYSQL select count(*) in one table if id exists in another table

Question

This should be really simple but I'm stuck on it and couldn't find the answer here.

I want to count the number of records in one table that match a given user_id if that user_id exists in another table, or if not return -1.

Something like:

SELECT COUNT(*) 
FROM table_1
WHERE user_id = (IF EXISTS table_2.user_id = '22')
, ELSE -1;

In summary, if table_2 contains a user_id of 22, then return the number of records in table_1 with a user_id of 22, or if not, return -1.

How do I do this please? (NB. Just to clarify, user_id is not a primary key in either table)

EDIT: adding table sample:

table_1
---------------------
user_id  |  item
---------------------
22       |  apple
23       |  orange
22       |  banana

table_2
---------------------
user_id  |  name
---------------------
20       |  billy
21       |  bob
22       |  thornton

So running the query I need with user_id = 21 would return 0, running with user_id = 22 would return 2 and with user_id = 23 would return -1.

Answer 1

Does this give you the result you need?

SELECT
CASE COUNT(*) WHEN 0 THEN -1 ELSE COUNT(*) END AS row_count
FROM table_1
WHERE user_id = 22
AND (
  SELECT COUNT(*)
  FROM table_2
  WHERE user_id = 22) >= 1;

It uses a CASE statement to implement the check and show -1 if no records are found. It uses a subquery in the WHERE clause to find the count of records in table_2, and checks that it is greater than or equal to 1.

There are a couple of ways to do this - it could possibly be done with a LEFT JOIN or an EXISTS, but I think this will work.