Solved

SQL Query Syntax Comparing Three Tables

Posted on 2011-03-17
13
361 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 200 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 300 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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