?
Solved

SQL Query Syntax Comparing Three Tables

Posted on 2011-03-17
13
Medium Priority
?
376 Views
Last Modified: 2012-06-21
Hi,

I have three tables:

1> uga_comment
2> cfp_demo
3> ecom_customers

The first two tables contain data for demo users. The third table contains data for demo users who have purchased.

What I'd like to do is return the firstname, lastname, email for all users in the first two tables whose email doesn't show up in the third table.

The SQL query I wrote looks like this:

SELECT DISTINCT firstname AS firstname, lastname AS lastname, email FROM uga_comment WHERE email NOT IN (SELECT DISTINCT email FROM ecom_customers)
UNION ALL  SELECT DISTINCT fname AS firstname, lname AS lastname, email FROM cfp_demo 
WHERE email NOT IN (SELECT DISTINCT email FROM ecom_customers) ORDER BY lastname ASC

Open in new window


Before I added the WHERE email NOT IN () part to both parts of the UNION it was returning the correct number of records. Trying to compare them screwed it up. My lack of knowledge screwed them up. :-)

Any pointers for correcting this issue?

Thank you.

Shawn.
0
Comment
Question by:Shawn Janes
  • 6
  • 6
13 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35159669
you can try like this.
SELECT * 
  FROM (SELECT firstname, 
               lastname, 
               email 
          FROM uga_comment 
        UNION 
        SELECT fname, 
               lname, 
               email 
          FROM cfp_demo) AS t1 
 WHERE email NOT IN (SELECT email 
                       FROM ecom_customers)

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35159680
or this.
SELECT * 
  FROM (SELECT firstname, 
               lastname, 
               email 
          FROM uga_comment 
        UNION 
        SELECT fname, 
               lname, 
               email 
          FROM cfp_demo) AS t1 
       LEFT JOIN ecom_customers t2 
         ON t1.email = t2.email 
 WHERE t2.email IS NULL

Open in new window

0
 
LVL 1

Assisted Solution

by:gmartinnc
gmartinnc earned 800 total points
ID: 35159706
try: SELECT DISTINCT firstname, lastname, Email FROM ( SELECT firstname AS firstname , lastname AS lastname , email FROM uga_comment UNION SELECT fname AS firstname , lname AS lastname , email FROM cfp_demo ) d WHERE email NOT IN ( SELECT DISTINCT email FROM ecom_customers ) ORDER BY lastname ASC
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:Shawn Janes
ID: 35159904
gmartinnc:

This worked!

I have a couple questions:

1> What is the lowercase d for  right before the WHERE?
2> I noticed that some of the email addresses are lower and uppercase and are duplicates. Can the case be forced to lower for all records then compared so the number of records returned are more accurate?

Thank you.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35160278
In that case, you can try this.
FYI - You don't need DISTINCT if you have UNION. UNION will take care of duplicate records.
SELECT * 
  FROM (SELECT firstname, 
               lastname, 
               email 
          FROM uga_comment 
        UNION 
        SELECT fname, 
               lname, 
               email 
          FROM cfp_demo) AS t1 
 WHERE lower(email) NOT IN (SELECT lower(email)
                       FROM ecom_customers)

Open in new window

0
 

Author Comment

by:Shawn Janes
ID: 35160332
Ok, maybe I mixed things up with my last response.

1> I do need distinct records.
2> Let me reword my question about duplicates...if a user takes a demo and enters shawnjanes@email.com, then comes back and enters ShawnJanes@EMAIL.com - will distinct handle that or will it show up twice - once as a lowercase and once as an upper? I just noticed I had uppercase email addresses sorted alphabetically first then I had lowercase email addresses sorted alphabetically at the bottom of the list. I want to make sure I don't have things mixed up.

What do you say?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35160395
try this.
SELECT * 
  FROM (SELECT firstname, 
               lastname, 
               lower(email) email 
          FROM uga_comment 
        UNION 
        SELECT fname, 
               lname, 
               lower(email) email
          FROM cfp_demo) AS t1 
 WHERE email NOT IN (SELECT lower(email)
                       FROM ecom_customers)

Open in new window

0
 

Author Comment

by:Shawn Janes
ID: 35160441
That works!

AND just one more question hopefully...

How can I get rid of invalid email addresses? For instance, email address not in _@_._ format? Is this possible?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1200 total points
ID: 35160474
check this.
SELECT * 
  FROM (SELECT firstname, 
               lastname, 
               lower(email) email 
          FROM uga_comment 
        UNION 
        SELECT fname, 
               lname, 
               lower(email) email
          FROM cfp_demo) AS t1 
 WHERE email NOT IN (SELECT lower(email)
                       FROM ecom_customers) 
   AND email NOT LIKE '%_@_%._%'

Open in new window

0
 

Author Comment

by:Shawn Janes
ID: 35160491
That kind of does what I want but in reverse. What I'm getting now are 212 records with invalid email addresses, I'd much rather exclude invalid email addresses from my query results.

Is that possible?
0
 

Author Comment

by:Shawn Janes
ID: 35160502
Ok, I just removed NOT and left in LIKE and it works fine now.

Thanks to you both very much.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35160506
my bad. it should be "LIKE" not "NOT LIKE"
SELECT * 
  FROM (SELECT firstname, 
               lastname, 
               lower(email) email 
          FROM uga_comment 
        UNION 
        SELECT fname, 
               lname, 
               lower(email) email
          FROM cfp_demo) AS t1 
 WHERE email NOT IN (SELECT lower(email)
                       FROM ecom_customers) 
   AND email LIKE '%_@_%._%'

Open in new window

0
 

Author Closing Comment

by:Shawn Janes
ID: 35160512
Great help, and fast! Thank you so much!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

807 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