Query

[Milly]

I'm trying to write a query to identify userids that are different for the same person using the following fields.... APP_EMP.USERID and Pub_All_Users_Appusers.USERNAME. This is a mass query that should identify all users with multiple userids. Any advice as to what I'm doing wrong would be greatly appreciated.

SELECT APP_EMP.LNAME, APP_EMP.FNAME, APP_EMP.USERID, Pub_All_Users_Appusers.USERNAME, Pub_All_Users_Appusers.PERSON_NUMBER
FROM APP_EMP INNER JOIN Pub_All_Users_Appusers ON APP_EMP.PERSON_NUMBER = Pub_All_Users_Appusers.PERSON_NUMBER
WHERE APP_EMP.USERID <=> Pub_All_Users_Appusers.USERNAME
ORDER BY APP_EMP.LNAME, APP_EMP.FNAME;

 

[James Goodman]

So to clarify you have the following tables & fields:

APP_EMP
-----------
UserID
Person_Number

Pub_All_Users_Appusers
--------------------------
UserName
Person_Number

In which case:

SELECT P.UserName, COUNT(A.UserID) AS NoIDs
FROM Pub_All_Users_Appusers P INNER JOIN App_Emp A
ON P.Person_Number = A.Person_Number
WHERE NoIDs > 1


Should give you a list of usernames with more than one UserID, & the number
which they have...

 

[Milly]

Thank you. I'll try it.