Postgres Join and return flag if a row exists

Question

I am very certain that this is possible in SQL but I am not sure how to implement this. I am using PostgreSQL

I have 2 tables

• users with columns id, name and created_date
• user_docs with columns id, value

I want to write a select query which returns all users table columns, along with another column called has_docs which indicates whether the user has any document rows in the user_docs table.

Can someone help?

Answer 1

You can left join the two tables and check if not null for the value

SELECT u.id,
       u.name,
       u.created_date,
       CASE WHEN ud.value IS NOT NULL
            THEN 'Y'
            ELSE 'N'
        END has_docs
  FROM users u
  LEFT JOIN user_docs ud
    ON u.id = ud.id