CODEWITHSUNDEEP
sqlmariadb
hiroya
hiroya

Reputation: 25

SQL set a column to true if some conditions meet

Got 3 tables, said

mail

{
  id: number,
  content: string
}

player

{
  id: number,
  name: string
}

is_mail_read

{
  mail_id: number,
  player_id: number
}

What I want to achieve:

Given a player_id, return the mails with a column is_read which is true (or 1) if there is a record in is_mail_read, false if there isn't.

For example:

[
  {  
    id: 2,
    content: "I am a mail",
    is_read: true
  },
  {  
    id: 3,
    content: "I am a mail too",
    is_read: false
  },...
]

I have tried left join but got no idea what to do next. The database is MariaDB.

Upvotes: 0

Views: 41

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would use exists:

select m.*,
       (exists (select 1
                from is_mail_read imr
                where imr.mail_id = m.id and
                      imr.player_id = ?  -- your desired player id
               )
       ) as read_flag
from mail m;

Upvotes: 1

fancyPants
fancyPants

Reputation: 51888

You're on the right track with using LEFT JOIN. Now you simply check, if for the record in the mail table there is a record in the player table by checking if the joined record is NULL or not.

I'm assuming that you want to display all mails and return the info if a mail is read for a specific player. Then you have to filter for the player in the JOIN clause, not the WHERE clause. If you do it in the WHERE clause, you implicitly turn the LEFT JOIN into an INNER JOIN. Except when you write the WHERE clause like 

WHERE p.name = "John" OR p.name IS NULL

So your query should look like this:

SELECT
m.id,
m.content,
IF(p.id IS NULL, false, true) AS is_read
FROM 
mail m
LEFT JOIN is_mail_read imr ON m.id = imr.mail_id
LEFT JOIN player p ON imr.player_id = p.id AND p.name = "John"

Upvotes: 1

Related Questions