[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL - records not in 2nd table

Posted on 2011-02-17
14
Medium Priority
?
486 Views
Last Modified: 2012-05-11
Hi,
I have a simple 1 to many relationship between 2 tables. table 1 has unique email addresses. table 2 is a history of users based on email address. So i have duplicates in table 2 with a date.
I need to work out which users have NOT loged in between 2 date ranges. I cannot get it working. Here is what i have at the moment. So i need to display users that have not logged into the system say for a specific month

SELECT DISTINCT u.email, h.email AS Expr1
FROM         tblUsers u LEFT OUTER JOIN
                      tblUserHistory H ON u.email = h.email
WHERE     (h.createDate >= '2010-07-01') AND (h.createDate <= '2010-07-02 00:00:00')
0
Comment
Question by:CraigLazar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
14 Comments
 
LVL 7

Expert Comment

by:kemi67
ID: 34916349
there are different ways to do that, the fastest depends on your data
One could be
SELECT tblUsers.email,
FROM         tblUsers where tblUsers.email not in (select h.email from tblUsers  h
WHERE     (h.createDate >= '2010-07-01') AND (h.createDate <= '2010-07-02 00:00:00'))
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 34916373
I don't see something wrong in there. Your where clause is using a locale specific date format, why not try with this?

SELECT DISTINCT u.email, h.email AS Expr1
FROM         tblUsers u LEFT OUTER JOIN
                      tblUserHistory H ON u.email = h.email
WHERE     (h.createDate >= '20100701') AND (h.createDate < '20100801')

Open in new window


This would give you all log ins from July 2010.

Bye, Olaf.
0
 
LVL 7

Accepted Solution

by:
kemi67 earned 1000 total points
ID: 34916408
Ops, i mispelled the second table

SELECT tblUsers.email,
FROM         tblUsers where tblUsers.email not in (select h.email from tblUserHistory h
WHERE     (h.createDate >= '2010-07-01') AND (h.createDate <= '2010-07-02 00:00:00'))
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:kemi67
ID: 34916434
Another query that gives the same result is

SELECT tblUsers.email
FROM         tblUsers
left join (select h.email from tblUserHistory h
WHERE     (h.createDate >= '2010-07-01') AND (h.createDate <= '2010-07-02 00:00:00')) D on d.email=tblUsers.email where D.email is null
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34916478
with the left join technique:
SELECT u.email
FROM tblUsers u 
LEFT OUTER JOIN tblUserHistory H 
ON u.email = h.email
  AND (h.createDate >= '2010-07-01') AND (h.createDate <= '2010-07-02 00:00:00')
WHERE h.email IS NULL

Open in new window

0
 
LVL 3

Expert Comment

by:QuinnDester
ID: 34916495
try this

SELECT
tblUsers.email
FROM
tblUsers u LEFT OUTER JOIN
                      tblUserHistory H ON u.email = h.email
WHERE
CONVERT(DATETIME,datename(yyyy,h.createDate ) + '-' + datename(mm,h.createDate ) + '-' + datename(dd,h.createDate ) , 102) between CONVERT(DATETIME,'2010-07-01', 102) and CONVERT(DATETIME,'2010-07-02', 102)
0
 
LVL 3

Expert Comment

by:QuinnDester
ID: 34916602
you do realise there is nothing between those dates so using the > and < will mean no results, using the between argument should include the dates you give
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34916605
The only way you are going to get the LEFT JOIN to return the correct results is by using the solution posted by angelIII posted here http:#a34916478

The reason you are not getting any results is because you are putting your condition for the table on the LEFT JOIN in the WHERE clause.  By doing that you are implicitly converting it to an INNER JOIN.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34917300
an alternative would be:
SELECT u.email
FROM tblUsers u 
WHERE NOT EXISTS( SELECT NULL
     FROM tblUserHistory H 
    WHERE u.email = h.email
      AND (h.createDate >= '2010-07-01') AND (h.createDate <= '2010-07-02 00:00:00')
 )

Open in new window

0
 
LVL 4

Author Comment

by:CraigLazar
ID: 34917791
wow thanks so much for all the responses
0
 
LVL 4

Author Closing Comment

by:CraigLazar
ID: 34917802
thanks a lot
perfect
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34918108
note that the accepted answer shall return the good results, but is likely not the most efficient method
if the good indexes are on the tables, my 2 suggestions shall execute faster, which I leave to your tests.
0
 
LVL 7

Expert Comment

by:kemi67
ID: 34918306
Even my  third post is faster than the accepted one
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34923099
>>note that the accepted answer shall return the good results, but is likely not the most efficient method<<
Absolutely.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

656 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