Search code examples
sqljoinmany-to-many

SQL. The intricate JOIN between two tables

I have 2 tables:users and subscribers. The relationship of the user to the subscriber is a many-to-many.

table users

table subscribers

I know how to get all users:

SELECT * FROM users

And I also know how to get subscribers to a given user by his id (for example id=1):

SELECT * FROM users LEFT JOIN subscribers ON id = user_id WHERE user_id = 1

But I need these results are not separately but together.

That is, I want to create a query to get all users. But in addition to their properties, result of the query must contain a property that shows each user is subscriber or not. Something like this:

table subscribers

And of course I would like to avoid the use of subqueries. It would be great to use only a JOINs.If this situation it is possible.

Thanks in advance!

Solution

  • You can do:

    SELECT u.*, 
        CASE WHEN s.user_id IS NULL THEN 'false' ELSE 'true' END as IsSubscriber 
FROM users u 
   LEFT JOIN subscribers s ON u.id = s.user_id

    ...since s.user_id will be made null by the left join when the user is not in the subscribers table.