Select column as true / false if the keypair exists in another table

I have currently these tables. Server: MariaDB

(I removed some columns to make it more simple)

Person table:

id     |      username     |     password    
-------------------------------------------------
1      |      hamster      |      strongpw
2      |      user2        |      password2

Snippet table:

id        |        title        |        description   
--------------------------------------------------------
1         |       BestTitle     |        GoodDesc
2         |       Test2         |        Test2Desc
3         |       Test3         |        Test3Desc

Favourite table:

person_id     |     snippet_id    
------------------------------------
1             |        1

I try to achieve this result

Test with Person id = 1

snippet.id    |    snippet.title    |    is_favourite (check favourite table)
--------------------------------------------------------
1             |     BestTitle       |    true
2             |     Test2           |    false
3             |     Test3           |    false

Test with Person id = 2

snippet.id    |    snippet.title    |    is_favourite  (check favourite table)
--------------------------------------------------------
1             |     BestTitle       |    false
2             |     Test2           |    false
3             |     Test3           |    false

I tried to do it with IN, JOINs, nested queries.

Answer

Is this what you want?

select s.id, s.title,
       (exists (select 1
                from Favourite f
                where f.snippet_id = s.id and f.person_id = ?
               )
       ) as is_favourite
from snippet s;

The ? is the placeholder for your person id of interest.

Advertisement

Answer