Question 113482 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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;
};
Tags:

3

Answers


  1. 0

    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 = ?
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    It write its in this way…I am using table name "book" and "bookshelf"
    and Status be 0 when book in not available in "bookshelf" table

    SELECT bookshelf.`id_bookshelf`,bookshelf.name_bookshelf,book.id_book,
(CASE WHEN book.id_book IS NULL THEN 0 ELSE 1 END) as status
FROM `bookshelf` 
LEFT JOIN book ON bookshelf.id_bookshelf = book.id_bookshelf
GROUP BY bookshelf.id_bookshelf

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search