• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Using UNION to join to SELECT statements with WHERE then variable='columname';

Hi, well this is whatI tried...

SELECT * FROM users WHERE email='$email'      
 UNION             
 SELECT * FROM temp_members_db  WHERE email='$email'

I want to check if a new registrations email address is already owned by an existing member and possibly a member awaiting activation which are stored in seperate table. I will need to do the same with username.

Am I way off or on right track?
thanks
0
Ryan Bayne
Asked:
Ryan Bayne
  • 2
  • 2
2 Solutions
 
JesterTooCommented:
That could work if both tables have the exact same schema (structure).  If not, you may want to just select a single common column.  I presume you're just checking the count of rows in the result set.

You didn't mention which DBMS you're using.  There are a couple of other potential issues with this simple approach...

1.  Depending on your DBMS's "case sensitivity"  John.Doe@somewhere.com may not match john.doe@Somewhere.com.

2.  john.doe@somewhere.com may not match john.doe@somewhere.com#### (where the #'s represent trailing spaces)

3.  john.doe@somewhere.com definitely won't match ###john.doe@somewhere.com (where the #'s represent leading spaces)

The last two issues are probably best handled by "trimming" the data before it is put into the tables and trimming any comparison strings.

If case sensitivity needs to be addressed, force all data to be either upper or lower as you're putting it into the tables.
0
 
Ryan BayneWordPress DeveloperAuthor Commented:
Yes just a count of rows. Never would have considered difference in table structure so will defune the column names see whathappens.

cheers
0
 
folderolCommented:
SELECT * FROM users WHERE email='$email'      
 UNION            
 SELECT * FROM temp_members_db  WHERE email='$email'

The above syntax will eliminate from the results any entries in temp_members_db that are also in users.  Every column must match.  If you have a unique primary key then no matches will exist, so this will have the same effect as UNION ALL.

select email from users WHERE email='$email'      
 UNION            
SELECT email FROM temp_members_db  WHERE email='$email'

or something like it will work as a union regardless of keys.  Again, only temp_member emails not in users will appear.


What I would do is

select * FROM temp_members_db
join
users on
temp_members_db.email = users.email
where
temp_members_db.email = '$email'

This is an intersection and only shows temp_member emails that are already in users table.
Your syntax may differ, you don't mention your DB.
0
 
JesterTooCommented:
folderol,

I think what he's trying to discover is if the $email value exists in either table then he wants to tell the potential new user that the email address he submitted already exists either as an activated member or one awaiting activation.
0
 
Ryan BayneWordPress DeveloperAuthor Commented:
Yes this is what I needed. Another something learned, define everything else have matching structures.

Cheers for the help. I did manage to do it from the first comment but the 2nd adds to the question so will issue some points.
SELECT email FROM users WHERE                             
email='$email'
UNION 
SELECT email FROM temp_members_db
WHERE email='$email'"

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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