Solved

SQL - records not in 2nd table

Posted on 2011-02-17
14
478 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
  • 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
 
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 142

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 142

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 142

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

12 Experts available now in Live!

Get 1:1 Help Now