erwingosepa
asked on
How to check email address in another table if it exists in a single sql statement
I need to figure out a way to check if an email address exists in another table.
for example:
Table Users
id Name Email_address
1 John john@hotmail.com
2 Maria maria@hotmail.com
3 Cijntje cijntje@hotmail.com
Table Membership
id Name Email_address etc etc...
1 John john@hotmail.com
2 Maria maria@hotmail.com
3 Cijntje cijntje@hotmail.com
4 Pedro pedro@hotmail.com
Result from the Query
id Name Already_User
1 John Yes
2 Maria Yes
3 Cijntje Yes
4 Pedro No
Here is example script
select
m.id,
m.name,
(CASE WHEN b.member is null then 'yes'
ELSE 'No'
END) as member
from Membership m
LEFT OUTER JOIN (
SELECT u.id,u.email_address as member FROM users u
) b ON b.id = m.id
I'm getting all of them as member. That is not good. At least one of them should be not member
for example:
Table Users
id Name Email_address
1 John john@hotmail.com
2 Maria maria@hotmail.com
3 Cijntje cijntje@hotmail.com
Table Membership
id Name Email_address etc etc...
1 John john@hotmail.com
2 Maria maria@hotmail.com
3 Cijntje cijntje@hotmail.com
4 Pedro pedro@hotmail.com
Result from the Query
id Name Already_User
1 John Yes
2 Maria Yes
3 Cijntje Yes
4 Pedro No
Here is example script
select
m.id,
m.name,
(CASE WHEN b.member is null then 'yes'
ELSE 'No'
END) as member
from Membership m
LEFT OUTER JOIN (
SELECT u.id,u.email_address as member FROM users u
) b ON b.id = m.id
I'm getting all of them as member. That is not good. At least one of them should be not member
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And you are sure there's no record (other then Pedro maybe) with id 4? Maybe you should check name and/or email instead?
ASKER
There is no " 4 Pedro" record in the Users Table. The only way to check is by the email address. Because there might be id or name that might exists but the email_address is what I would like to check.
Table Users
id Name Email_address
1 John john@hotmail.com
2 Maria maria@hotmail.com
3 Cijntje cijntje@hotmail.com
4 Boob boob@hotmail.com
5 Wisk wisk@hotmail.com
Table Membership
id Name Email_address etc etc...
1 John john@hotmail.com
2 Maria maria@hotmail.com
3 Cijntje cijntje@hotmail.com
4 Pedro pedro@hotmail.com
Table Users
id Name Email_address
1 John john@hotmail.com
2 Maria maria@hotmail.com
3 Cijntje cijntje@hotmail.com
4 Boob boob@hotmail.com
5 Wisk wisk@hotmail.com
Table Membership
id Name Email_address etc etc...
1 John john@hotmail.com
2 Maria maria@hotmail.com
3 Cijntje cijntje@hotmail.com
4 Pedro pedro@hotmail.com
You can't have gotten all users as being members in your original query, because the logic was (errornously) reversed. For the same records you should have gotten the inverse result of what you expected, or all as not being members.
robert_schutts' query should work. You have an issue with your example data, maybe your real data is different from what you posted.
robert_schutts' query should work. You have an issue with your example data, maybe your real data is different from what you posted.
ASKER
User Table is being populated by other procedure then the Membership Table. So we have to ignore the id and the name because they may be the same or different. The only field that I can check is the email address. In this case Pedro email address is not in the Users table therefore it should indicate in the query "No". At this moment running the query it indicating "Yes" which is not true.
ASKER
I think I got it. I change the "id" to "email_address"
select
m.id,
m.name,
(CASE WHEN u.email_address is null then 'No' ELSE 'Yes'
END) as AlreadyMember
from Membership m
LEFT OUTER JOIN users u ON u.email_address = m.email_address
select
m.id,
m.name,
(CASE WHEN u.email_address is null then 'No' ELSE 'Yes'
END) as AlreadyMember
from Membership m
LEFT OUTER JOIN users u ON u.email_address = m.email_address
Of course - you just told us that you cannot rely on id or name, only on email address, but compared the id ... With the example data provided, it doesn't make a difference, but with your real data it does.
Uhm, tooting my own horn here but that's what I said before: #35456009 but I'm glad it's working now.
erwingosepa: thanks for accepting but have another look please: it's important for future reference that you identify assists, even your own last remark (i guess you can't give yourself points but still) because that contains the actual solution.
Also, for your own understanding, if you have the time, have a look at the output of the query:
select * from Membership m
LEFT OUTER JOIN users u ON u.email_address = m.email_address
which may clarify completely what's going on, and changing which 'id' reference does, and which one doesn't matter.
Also, for your own understanding, if you have the time, have a look at the output of the query:
select * from Membership m
LEFT OUTER JOIN users u ON u.email_address = m.email_address
which may clarify completely what's going on, and changing which 'id' reference does, and which one doesn't matter.
ASKER
will do, Thank you very much.
ASKER