We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to check email address in another table if it exists in a single sql statement

Medium Priority
875 Views
Last Modified: 2012-06-27
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




 





Comment
Watch Question

Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I'm still getting all of them as member "Yes". Even the  "4    Pedro   pedro@hotmail.com" in membership table is getting AlreadyMember Yes.



Robert SchuttSoftware Engineer
CERTIFIED EXPERT

Commented:
And you are sure there's no record (other then Pedro maybe) with id 4? Maybe you should check name and/or email instead?

Author

Commented:
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

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
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.

Author

Commented:
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.

Author

Commented:
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
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
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.
Robert SchuttSoftware Engineer
CERTIFIED EXPERT

Commented:
Uhm, tooting my own horn here but that's what I said before: #35456009 but I'm glad it's working now.
Robert SchuttSoftware Engineer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
will do, Thank you very much.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.