[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • Last Modified:

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




 





0
erwingosepa
Asked:
erwingosepa
  • 5
  • 4
  • 2
1 Solution
 
Robert SchuttSoftware EngineerCommented:
Can you try this:

select
m.id,
m.name,
(CASE WHEN u.id is null then 'No' ELSE 'Yes'
END) as AlreadyMember  
from Membership m
LEFT OUTER JOIN users u ON u.id = m.id
0
 
erwingosepaAuthor 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.



0
 
Robert SchuttSoftware EngineerCommented:
And you are sure there's no record (other then Pedro maybe) with id 4? Maybe you should check name and/or email instead?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
erwingosepaAuthor 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

0
 
QlemoDeveloperCommented:
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.
0
 
erwingosepaAuthor 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.
0
 
erwingosepaAuthor 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
0
 
QlemoDeveloperCommented:
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.
0
 
Robert SchuttSoftware EngineerCommented:
Uhm, tooting my own horn here but that's what I said before: #35456009 but I'm glad it's working now.
0
 
Robert SchuttSoftware EngineerCommented:
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.
0
 
erwingosepaAuthor Commented:
will do, Thank you very much.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now