Solved

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

Posted on 2008-06-25
5
221 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 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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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