using (not in) in inner join sql

Question

i have two tables

1) tbluser 2) userlogin

tbluser consist of

username and email

and userlogin looks like

create table userlogin
(
username varchar(50),
[date] datetime
)

i want to select email from tbluser and validate userlogin(table 2)

i want email of the employees from table 1 which are not in table 2 with selected date range

so i created a procedure

alter proc TMSautomail
@datefrm datetime,
@dateto datetime
as
begin
SELECT
     tblUser.Email
from
     tblUser
         inner join
     userlogin
         on
      tblUser.UserName not in userlogin.username
where userlogin.date between @datefrm and @dateto
end

it is showing me error, when i change the procedure to this

alter proc TMSautomail
@datefrm datetime,
@dateto datetime
as
begin
SELECT
     tblUser.Email
from
     tblUser
         left outer join
     userlogin
         on
      tblUser.UserName = userlogin.username
where userlogin.date between @datefrm and @dateto and userlogin.username is null
end

the procedure returns blank column,

i want to fetch email of table one and validate with username which are not in table 2,

my second table is consist of one entry

it should return all rows of table one

i used that also but when i add where clause it is showing all rows — , Feb 13 '17 at 05:29

score 0 · Accepted Answer · answered Feb 13 '17 at 05:33

Use NOT IN and a sub-query:

CREATE PROCEDURE TMSautomail(
    @datefrm datetime,
    @dateto datetime
)
AS
BEGIN

    SELECT u.Email
    FROM tblUser u
    WHERE u.Email NOT IN (
        SELECT Email
        FROM tblUser u1
            INNER JOIN UserLogin l
                ON u1.Username = l.Username
        WHERE l.Date BETWEEN @datefrm AND @dateto
    )

END

score 0 · Answer 2 · answered Feb 16 '17 at 05:34

0

try this,

SELECT u.Email
    FROM tblUser u
    WHERE  NOT EXISTS (
        SELECT Email
        FROM  UserLogin l
                WHERE u1.Username = l.Username
        AND l.Date BETWEEN @datefrm AND @dateto
    )

answered Feb 16 '17 at 05:34

KumarHarsh

5,046
1
18
22

using (not in) in inner join sql

2 Answers2