CODEWITHSUNDEEP
mysqlsql
randombits
randombits

Reputation: 48450

MySQL select if exists but show value with no if it doesn't exist

Given a simple query like the following:

SELECT * from foo WHERE id IN (ids)

Say I pass the ids 1,2,3 and id 1 and 2 exist and 3 does not. How do I write this query so that 3 rows still show up, but a value of 'yes' shows up for 1 and 2, and a 'no' for 3?

My query would only return data for records with id 1 and 2, but I want it to show up for every id I pass in, but signify the ones that aren't in the table with a simple 'no'

Upvotes: 0

Views: 2126

Answers (1)

sticky bit
sticky bit

Reputation: 37472

You'd need to create a relation containing all the IDs and then left join foo on the IDs. In a CASE you can then check for the ID from foo being NULL and output 'no' if it is, 'yes' otherwise.

SELECT x.id,
       CASE
         WHEN f.id IS NULL THEN
           'no'
         ELSE
           'yes'
       END yesorno
       FROM (SELECT 1 id
             UNION ALL
             SELECT 2 id
             UNION ALL
             SELECT 3 id) x
            LEFT JOIN foo f
                      ON f.id = x.id;

Upvotes: 1

Related Questions