Comparison query help?!

[CJAI]

This may be a bit elementary but I have 2 tables (A & B), related buy a foreign key called 'username'.

I want to select ALL of the records in table B that are NOT IN table A.

What'e the best way of doing this? I tried the following and had no success (pulled empty recordset).

SELECT DISTINCT(username) As Uname
FROM tbl_timesheets_excel
WHERE username NOT IN (SELECT Email FROM tbl_TimeSheetUsers)

Thanks much!!!

 

[nomadicalloy]

Please try this :

SELECT DISTINCT(username) As Uname
FROM tbl_timesheets_excel
WHERE username NOT IN (SELECT username FROM tbl_TimeSheetUsers)

I am assuming table-A and table-B has username as common field.


SQL Help Blog

http://www.xpherion.com/blog.asp

 

[CJAI]

Same thing - pulls nothing!!

 

[CJAI]

I'm sorry I didn't clarify but username is equal to email. So it's essentially the same query.

Any other ideas??

Thanks guys.

 

[SQLBill]

If nomadicalloy's query didn't return anything for you, then it's because ALL of the UserNames in tbl_timesheets_excel are also in tbl_TimeSheetUsers.

Do you know for a fact that there are usernames in tbl_timesheets_excel that are not in tbl_TimeSheetUsers?

If so, maybe knowing the structure of your tables and having some sample data might help us.

-SQLBill

Posting advice: FAQ481-4875

 

[CJAI]

Good point SQL Bill, I will double check. 1 sec.

 

[CJAI]

OK, ALL of the data in tbl_timesheets_excel is NOT in tbl_TimeSheetUsers.

tbl_timesheets_excel
--------------------
username
john.doe

tbl_TimeSheetUsers
--------------------
email
john.doe

Want to find all users in tbl_timesheets_excel that are NOT in tbl_TimeSheetUsers.

tbl_TimeSheetUsers is a master list of company users and we want to find out all the users who didn't enter their timesheets.

Thanks!!

 

[SQLBill]

Try this:

SELECT COUNT(DISTINCT UserName)
FROM tbl_timesheets_excel
GO
SELECT COUNT(DISTINCT UserName)
FROM tbl_timesheetusers
GO

That will count the distinct usernames from each table. If both numbers are the same, then (obviously) you don't have any 'unmatched' usernames.

-SQLBill

Posting advice: FAQ481-4875

 

[CJAI]

Well I added a dummy value in tbl_TimeSheetUsers that is NOT in tbl_timesheet_excel and it still did not work??

 

[amrita418]

The reason could be that the Email column in Timesheets has nulls in which case NOT IN fails to fetch anything.

Try this instead:

select distinct username from TableA A
where not exists (select 'x' from TableB b
                  where a.username = b.email)

Regards,
AA

 

[SQLBill]

I suggest:

SELECT DISTINCT UserName
FROM tbl_timesheets_excel
 WHERE UserName NOT IN (SELECT DISTINCT Email
                        FROM tbl_timesheetusers)

Which is the same as nomadicalloy came up with and should return values from tbl_timesheets_excel if they are NOT IN tbl_timesheetusers.

(BTW- your database IS Microsoft SQL Server, correct?)

-SQLBill

Posting advice: FAQ481-4875

 

[CJAI]

Yes it's SQL.

Amrita: No NULLS in the table, tried your suggestion and didn't work

SQLBill: That's the exact same query I have in Query Analyzer as we speak and it's not working.

Could it be that the query is case sensitive on the username and email columns??

I really appreciate all of yoru help.

 

[SQLBill]

Could it be that the query is case sensitive on the username and email columns??

Yes, it could be....but that really depends on your database collation. Is your database collation set for CASE-SENSITIVE?

If so, try:

SELECT DISTINCT UPPER(UserName0
FROM tbl_timesheets_excel
 WHERE UserName NOT IN (SELECT DISTINCT UPPER(Email)
                        FROM tbl_timesheetusers)

-SQLBill

Posting advice: FAQ481-4875

 

[CJAI]

Still no luck. This is driving me crazy?! This should be a pretty simple query.

 

[SQLBill]

BTW- that 0 after UserName in the first line should have been a close parenthesis ')'.

Did you run the count scripts that I posted?

-SQLBill

Posting advice: FAQ481-4875

 

[amrita418]

Case sensitiveness seems to be the only reason you do not get the desired output. Try SQLBill's suggestions and let us know if that helps.

Regards,
AA

 

[CJAI]

Tried that too and it still didn't work

 

[SQLBill]

Try this:

SELECT DISTINCT tte.UserName,
                ttu.Email
FROM tbl_timesheets_excel tte
  FULL OUTER JOIN tbl_timesheetusers ttu
  ON tte.UserName = ttu.Email

That should return a list with the data from each table. Where there isn't a match, the column should have NULL.

tte.UserName ttu.Email
john.doe john.doe
jane.smith NULL -- doesn't exist in ttu.Email
NULL jim.jones --doesn't exist in tte.UserName

-SQLBill

Posting advice: FAQ481-4875

 

[amrita418]

Can you be more specific what you mean by it did not work?

Run the query and see what you get?

select    a.username, 
          b.email 
from      tableA a 
          inner join TableB b
          on (a.username = b.email)

Does the output seem right?

Regards,
AA