CODEWITHSUNDEEP
mysqlsqlselectcountwhere-clause
Colin R. Turner
Colin R. Turner

Reputation: 1415

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

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.

Upvotes: 3

Views: 3847

Answers (2)

Mureinik
Mureinik

Reputation: 311163

One neat (dirty?) trick you can use is to select counts from both tables and cross join the results. Since you're querying aggregate functions with no group by clauses, you're guaranteed to get one row in each result:

SELECT     CASE cnt_2 WHEN 0 THEN -1 ELSE cnt_1 END 
FROM       (SELECT COUNT(*) AS cnt_1
            FROM   table_1
            WHERE  user_id = 22) t
CROSS JOIN (SELECT COUNT(*) AS cnt_2
            FROM   table_2
            WHERE  user_id = 22) s

Upvotes: 1

bbrumm
bbrumm

Reputation: 1352

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.

Upvotes: 1

Related Questions