Solved

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

Posted on 2008-06-25
5
216 Views
Last Modified: 2010-03-20
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
Comment
Question by:Ryan Bayne
  • 2
  • 2
5 Comments
 
LVL 21

Accepted Solution

by:
JesterToo earned 200 total points
ID: 21871333
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
 
LVL 2

Author Comment

by:Ryan Bayne
ID: 21871440
Yes just a count of rows. Never would have considered difference in table structure so will defune the column names see whathappens.

cheers
0
 
LVL 19

Assisted Solution

by:folderol
folderol earned 150 total points
ID: 21871475
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
 
LVL 21

Expert Comment

by:JesterToo
ID: 21871523
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
 
LVL 2

Author Comment

by:Ryan Bayne
ID: 21871650
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now