CODEWITHSUNDEEP
mysqlsql
Maximilian Krause
Maximilian Krause

Reputation: 1155

If value exists in other table, return another value from that table

I currently have the following SQL query:

SELECT video_calls.initiated_user_id AS user_id,
       (CASE
            WHEN EXISTS
                   (SELECT *
                    FROM patients
                    WHERE patients.id = video_calls.initiated_user_id)
            THEN 'patient'
            ELSE (CASE
                      WHEN EXISTS
                             (SELECT *
                              FROM backend_users
                              WHERE backend_users.id = video_calls.initiated_user_id)
                      THEN "%%backend%%"
                      ELSE "unknown"
                  END)
        END) AS user_type
FROM video_calls
WHERE id='7f350a98-93d3-4d21-80a8-6cda3e47a4c0'
UNION
SELECT user_id,
       user_type
FROM channel_joins
WHERE channel_id='7f350a98-93d3-4d21-80a8-6cda3e47a4c0'

In the line, where it currently says THEN "%%backend%%" I'd like to return the column backend_users.backend_type instead, for the corresponding row where the value video_calls.initiated_user_id has been found. I suppose I need to work with a JOIN here, but I currently can't figure out where exactly.

Upvotes: 1

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You are already using a correlated subquery. You can use that to get the value:

        ELSE (SELECT COALESCE(MAX(bu.backend_type), 'unknown')
              FROM backend_users bu
              WHERE bu.id = video_calls.initiated_user_id
             )

Note the use of MAX(). This ensures that exactly one value is returned. If no rows match, the MAX() returns NULL, so 'unknown' is returned.

This has one slight nuance from your pseudo-code. If the matching row is NULL, then this returns 'unknown' rather than NULL. If that is an issue, the logic in the subquery can be tweaked.

Upvotes: 1

Related Questions