CraigLazar
asked on
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')
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')
I don't see something wrong in there. Your where clause is using a locale specific date format, why not try with this?
This would give you all log ins from July 2010.
Bye, Olaf.
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')This would give you all log ins from July 2010.
Bye, Olaf.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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
try this
SELECT
tblUsers.email
FROM
tblUsers u LEFT OUTER JOIN
tblUserHistory H ON u.email = h.email
WHERE
CONVERT(DATETIME,datename(
SELECT
tblUsers.email
FROM
tblUsers u LEFT OUTER JOIN
tblUserHistory H ON u.email = h.email
WHERE
CONVERT(DATETIME,datename(
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
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.
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.
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')
)ASKER
wow thanks so much for all the responses
ASKER
thanks a lot
perfect
perfect
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.
if the good indexes are on the tables, my 2 suggestions shall execute faster, which I leave to your tests.
Even my third post is faster than the accepted one
>>note that the accepted answer shall return the good results, but is likely not the most efficient method<<
Absolutely.
Absolutely.
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'))