Solved

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
date diff with Fiscal Calendar 4 77
TSQL Challenge... 7 44
Help with Oracle IF statment 5 36
Search query matching words 20 38
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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