Clever Geek Handbook

Custom output column based on whether a value exists in any record of another, unrelated table

I have a bunch of tables and I am trying to pull different pieces of information out of each one. One of the pieces of information I need is if the customer has signed up for the newsletter, but there are no email bind links between the two tables. So I'm trying to show a custom column (like SubscribedToNewsletter) that shows if the customer's email address exists in the Subscriptions.Email values ​​(i.e. True / False).

Customer
===============
Email

Subscriptions  
===============
Email

I've tried things with CASE

and EXISTS

to try and create a fake column based on whether a value exists in a column of another table, but it doesn't produce any fruits.

SELECT 
CASE WHEN Subscriptions.Email = Customer.Email THEN 'True' ELSE 'False' END
FROM Customer
INNER JOIN Subscriptions ON 1=1
WHERE EXISTS (SELECT 1 FROM Customer WHERE Subscriptions.Email = Customer.Email)
+3


source to share


1 answer


Use a correlated subquery to count the number of customer subscription numbers:



select c.*,
       case when (select count(*) from Subscriptions s
                  where s.Email = c.Email) > 0 then 'True'
            else 'False'
       end
from customers c
+4


source






More articles:


All Articles