How chceck is id is used in other column in same table

I’m using MariaDB 10.3 and I have table like:

post_id post_content post_user_id post_shared
1       Test1        1             0
2       Test2        2             0
3       Test2        1             2

post_shared = 0 means this is original post and is not shared.

I am looking for a way to know if the post has been shared by a particular user (coming from post_user_id). Example output is as follows:

post_id isShared                     ( post_user_id 1)
1       0 (false)
2       1 (true)
3       0 (false)

I tried a LEFT JOIN to the same table and checked using if condition, but the code is returning me erroneous value.

Thx all for help 🙂

Answer

You can add a boolean flag using a correlated subquery or left join. If there are no duplicates:

select t.*, (ts.post_id is not null) as isShared
from t left join
     ts
     on ts.post_shared = t.post_id and
        ts.post_user_id = 1;

As a correlated subquery, this looks like:

select t.*,
       (exists (select 1
                from ts
                where ts.post_shared = t.post_id and
                      ts.post_user_id = 1
               )
       ) as isShared