Solved

SQL - records not in 2nd table

Posted on 2011-02-17
14
479 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 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

27 Experts available now in Live!

Get 1:1 Help Now