Query for checking an email in 2 different tables

[brad62]

I'm using Dreamweaver now to build a site. A lot of things are written by itself and some things really don't.
Now I'm trying to build a page where the user is adding a name and an e-mail.

I want my page to check that the e-mail isn't already in my table and that is pretty simple.
But, now I want this one to check if the e-mail is in either table_users OR table_members

So, if someone is filling in the form with their name, address and e-mail I want the query to check both tables if the e-mail isn't already in the system.
What would a query like that look like?

I thought this would work, but it doesn't.

  $loginUsername = $_POST['email'];
  $LoginRS__query = sprintf("SELECT * FROM table_users, table_members WHERE member_email=%s OR user_email=%s", GetSQLValueString($loginUsername, "text")); 

[2ndmouse]

There appears to be a similar problem (resolved) at stackoverflow

Hope that helps

[molona]

You can't do that query... either both tables are very similar in structure and you want to show the results as if they were only one table, or those tables have some kind of relationship using primary and foreign keys

I think that you're in the first case and that means using a UNION

I suggest that you don't use the wild card (*) and take a bit of more time to write the name of the fields you need. It improves speed.

I guess that both table have a field named "email". Because the name of the field is not exactly the same in both tables (a pratice that I follow, I never use the exact same name in two tables) you'll need an alias.

SELECT users_email AS email FROM table_users
UNION [ALL]
SELECT members_email AS email FROM table_members
WHERE
email LIKE '%s'

I wrote [ALL] beause this is optional. The default behaviour of a UNION is to select all the records in the table, anyway.

Because you're using patterns, you need to use LIKE and not = when you write your WHERE clause

This should work

[molona]

There appears to be a similar problem (resolved) at stackoverflow

Hope that helps

He doesn't necessarily need to do a DISTINCT and it looks that there's no place for a JOIN (outer or not) because it seems that both tables don't have a relationship (at least, not directly)

[brad62]

I solved it in my own little strange way. I did pickup the first hits and then, IF it found one my script did a new search in the next table, otherwise continue another way.
So, I solved it with two queries and IF ELSE. It works fine, but I guess, not the best way.

[molona]

You solved it and that's good. Well done. You can try to use the UNION and see if there's any advantage