SQL Query Syntax Comparing Three Tables

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.
Shawn JanesAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
gmartinncConnect With a Mentor Commented:
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
 
Shawn JanesAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
Shawn JanesAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
Shawn JanesAuthor Commented:
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
 
Shawn JanesAuthor Commented:
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
 
Shawn JanesAuthor Commented:
Ok, I just removed NOT and left in LIKE and it works fine now.

Thanks to you both very much.
0
 
SharathData EngineerCommented:
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
 
Shawn JanesAuthor Commented:
Great help, and fast! Thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.