How to return '1' if data exists and '0' if there is no data with mysql CASE-CodePudding

I have a purchase table and user_id is a foreign key.

If there is user_id in the purchase table, it returns 1, I want to return 0 if there is none.

I tried using the case statement, but it returns the following result.

{
     "data": []
}

Below is the query. Is there any other way? I need help. dao.js

const getUserPurchase = async(userId) => {
   const purchase = await myDataSource.query(
     `SELECT
       u.id,
       CASE
       WHEN p.user_id is null
       THEN '0'
       ELSE '1'
       END AS userPurchase
     FROM purchase p
     JOIN users u ON p.user_id = u.id
     WHERE u.id = ?`,
     [userId],
   );
   return purchase;
};

CodePudding user response：

I'd write it this way:

SELECT
   u.id,
   COUNT(*) > 0 AS userPurchase
FROM users u
LEFT OUTER JOIN purchase p ON p.user_id = u.id
WHERE u.id = ?
GROUP BY u.id

CodePudding user response：

You want to SELECT all users and for those users who have purchased something, that is a corrsponding record in table purchase exists, you want to get a 1, for all others a 0.

SELECT u.id,
       CASE 
         WHEN EXISTS (SELECT 1 FROM purchase p WHERE p.user_id = u.id) THEN 1 ELSE 0
       END AS userPurchase
  FROM users u
 WHERE u.id = ?