Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-06-25
5
Medium Priority
?
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
JesterToo earned 800 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 600 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 22

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 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