Solved

SQL Query Syntax Comparing Three Tables

Posted on 2011-03-17
13
353 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…

911 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

22 Experts available now in Live!

Get 1:1 Help Now