wolfQueen wolfQueen - 15 days ago 6
SQL Question

SQL: Check if entry exists in one table to another and remove duplicates

The same question actually as this one:
sql: check if entry in table A exists in table B

Here are my tables, Employee and User

Employee User
EmpNo EmpNo
PositionCode


I just want to check if the EmpNo in table:Employee already exists in table:User
here's the code that ive used:

SELECT Employee.EmpNo, PositionCode
FROM Employee
WHERE NOT EXISTS (SELECT 1
FROM User
WHERE User.EmpNo= Employee.EmpNo)


Now with that query it displays the result,
But it also displays duplicate rows of PositionCode.

eg:

EmpNo PositionCode
E098 ER1
E712 ER1
E990 ER1


So yeah, I just need to get the PositionCode(in this ex its ER1) so I can display it to a dropdownList, and you dont want to display same items in a dropdownlist right?

Answer

I am still not sure what you want. But from my understanding you want distinct PositionCode which have EmpNo related to them. If that's what you want you can just select Distinct PositionCode as below.

SELECT DISTINCT PositionCode
FROM   Employee
WHERE  NOT EXISTS (SELECT 1 
                   FROM   User
                   WHERE  User.EmpNo= Employee.EmpNo)

Let me know if this is not what you are looking for.