Solved

SQL Query Syntax Comparing Three Tables

Posted on 2011-03-17
13
349 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 40

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 40

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
 

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 40

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 40

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 40

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 40

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now