CODEWITHSUNDEEP
mysqlsql
Hussain Fakhruddin
Hussain Fakhruddin

Reputation: 3272

Sql query to derive a column value based on the entry in another table

I have a person table with fields id, email.

And a subscription table with fields: person_id, category_id.

If a person is subscribed subscription table will have at-least one entry in the table.

I want to query person table with the 3rd column having 1 or 0 depending on whether person is subscribed or not.

select p.id, p.email , [is_subscribed] 
from person p

is_subscribed should be 1 if there is an entry in the subscription table else should be 0.

How can I achieve the above ?

Upvotes: 1

Views: 1812

Answers (4)

Sin
Sin

Reputation: 1864

select distinct person.id, person.email, cast((select count(id) from sub where id=1) as bit) from tab, sub where tab.id=1;

This is an MS SQL server query. Make necessary changes for mysql.

Upvotes: 0

podiluska
podiluska

Reputation: 51514

   select distinct p.id, p.email, ifnull(sign(subscription.id),0) as is_subscribed
   from
       person
           left join subscriptions
           on person.id = subscriptions.person_id

Upvotes: 0

s.webbandit
s.webbandit

Reputation: 17028

SELECT p.`id` , p.`email` , IF(COUNT(s.`id`)>0,1,0) AS count
FROM  `person` p
LEFT JOIN  `subscription` s ON s.`person_id` = p.`id`

Upvotes: 0

dweeves
dweeves

Reputation: 5615

Using a LEFT JOIN on subscription & a count (in a case of several subscriptions)

SELECT p.id,p.email,IF(COUNT(s.category_id)>0,1,0) as is_subscribed
FROM
person as p
LEFT JOIN subscription as s ON s.person_id=p.id
GROUP BY p.id

Upvotes: 1

Related Questions