[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

SQL - records not in 2nd table

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
CraigLazar
Asked:
CraigLazar
  • 4
  • 3
  • 2
  • +3
1 Solution
 
kemi67Commented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
kemi67Commented:
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
Independent Software Vendors: 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!

 
kemi67Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
QuinnDesterCommented:
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
 
QuinnDesterCommented:
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
 
Anthony PerkinsCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
CraigLazarAuthor Commented:
wow thanks so much for all the responses
0
 
CraigLazarAuthor Commented:
thanks a lot
perfect
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
kemi67Commented:
Even my  third post is faster than the accepted one
0
 
Anthony PerkinsCommented:
>>note that the accepted answer shall return the good results, but is likely not the most efficient method<<
Absolutely.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now