Solved

SQL - records not in 2nd table

Posted on 2011-02-17
14
484 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 29

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 250 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

719 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