MYSQL query (with joins and virtual values) -

MYSQL query (with joins and virtual values) -

i have 3 innodb tables: emails, websites , subscriptions.

emails table has id , email columns.

websites table has id , address columns.

subscriptions table has id, email_id , website_id columns.

what i'm tring supply email , homecoming table columns address , subscribed. former list of addresses in websites table , latter gets value 1 if supplied email address has occurence in subscriptions table website_id set website, or 0 otherwise. i'm willing retain websites if user not found.

the point i'm stuck should alter value of virtual column subscribed 0 1 when email has record.

here's query far. know how this?

select `address`, "0" `subscribed` /* 0 becomes 1 websites email has subscribed */ `websites` left bring together ( select s.`website_id` `subscriptions` s right bring together ( select `id` `email_id` `emails` `email`='someone@mail.com' limit 1) e on s.`email_id`=e.`email_id`) l on l.`website_id`=a.`id`

and here illustration outputs desired values subscribed column:

if email not found in emails table rows value 0 if email found in emails table... if not found in subscriptions table rows value 0 if found in subscriptions table, appropriate address rows value 1

let me know if couldn't wxplain well. anytbody know should alter in query?

thanks in advance.

select w.address, case when s.id null 0 else 1 end subscribed websites w left bring together subscriptions s inner bring together emails e on s.email_id = e.id , e.email = 'someone@mail.com' on w.id = s.website_id

you come subscribed value way, bit more concise less obvious.

select w.address, coalesce(s.id/s.id, 0) subscribed websites w left bring together subscriptions s inner bring together emails e on s.email_id = e.id , e.email = 'someone@mail.com' on w.id = s.website_id

mysql join