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

Question

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 :)

score 1 · Accepted Answer · answered Jul 07 '19 at 11:14

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

Thx @Gordon Linoff :) This work :) I cant understand how subqueris work :) — Patryk Tchórzewski, Jul 07 '19 at 11:21

score 0 · Answer 2 · answered Jul 07 '19 at 11:09

0

use corealted subquery

select t1.* from table_name t1
where exists( select 1 from table_name t2 where t1.post_user_id=t2.post_user_id
                          and post_shared<>0)

answered Jul 07 '19 at 11:09

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 3 · answered Jul 07 '19 at 11:14

0

With exists:

select
  t.post_id,
  case when exists (
      select 1 from tablename
      where post_id <> t.post_id
      and post_shared = t.post_id
    ) then 1 
    else 0
  end isShared                     
from tablename t

answered Jul 07 '19 at 11:14

forpas

160,666
10
38
76

score 0 · Answer 4 · answered Jul 07 '19 at 11:29

0

Using left join

SELECT t1.post_id, IF(t2.post_id IS NULL, FALSE, TRUE)
FROM Test as t1
LEFT JOIN Test as t2 ON (t1.post_id = t2.post_shared and t2.post_user_id = 1)
order by t1.post_id;

answered Jul 07 '19 at 11:29

Tejash JL

66
3

score 0 · Answer 5 · answered Jul 07 '19 at 11:36

0

Solution using cases and a text-based Output:

SELECT *,
CASE

    WHEN post_shared > 0 THEN 'This story has been shared' 
    ELSE 'This story has not been shared' 

END AS share_status
FROM Test

answered Jul 07 '19 at 11:36

Sebastian Wiszowaty

157
9

you dont understend problem ;) Your output is look like: post_id(1) not shared, post_id(2) not shared, post_id(3) shared. – Patryk Tchórzewski Jul 08 '19 at 08:22

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

5 Answers5