Q:

MySQL generate a column based on if condition

I have 3 tables like following
User      Advertisement      Payment

id        id                 id
name      title              user_id
address   description        ad_id
                             payment_type

For these tables I want to generate a table like below for the logged in users ,regardless of payment_type:
id    title        is_paid
1     first ad      0 //if not paid
2     second ad     1 //paid
3     third ad      1 
4     fourth ad     0 
5     fifth ad      1 

So far I wasn't able to do anything as I have no idea how to add an extra column in mysqlm or to use if condition in query.
I'm not asking you to write code for me, but can you let me know what approach to use for this?

A:

If I'm understanding correctly, you can use an outer join with a case statement:
select a.id, a.title, 
    case when p.id is not null then 1 else 0 end is_paid
from advertisement a
    left join payment p on a.id = p.ad_id and p.user_id = 123

