CODEWITHSUNDEEP
sql
OysterMaker
OysterMaker

Reputation: 319

Comparing two table columns to see what one table is missing

So currently I have two tables, 

Servers              Admins
-------             --------
ServerID             AdminID
ServerName           AdminFirstName
Server location      AdminFirstName 
AdminID

What I am currently trying to do is go through the AdminID column in the Servers table and compare it to the AdminID column in the Admins table. The comparison I'm trying to make is "What admins have not been assigned to a server yet?", and "What servers do not have an admin assigned to them yet?"

I have tried the following (to attempt the first question):

SELECT CONCAT(AdminLastName,',',AdminFirstName)
FROM Admins
WHERE AdminID NOT IN(SELECT AdminID FROM Servers)

The query is returning 0 results, when it should be returning a few. Since both questions can be answered with virtually the same logic, I just need a push in the right direction as to why this is not working.

Thanks in advance

Upvotes: 0

Views: 73

Answers (2)

berty
berty

Reputation: 2206

Your query seems to be correct.

It can be due to several things :

  • All admins are actually assigned to a server

  • You dont execute query on the right database...

  • There is a problem with ID column's contents : if it's not INTEGER it can contain spaces or other non-desired characters in one of the two tables, and it does not match for the database system. Try to TRIM() the Id's for example

  • Depending on your database system, it can be better to write table names explicitly since you have the same field name in multiple tables

For example :

SELECT CONCAT(AdminLastName,',',AdminFirstName)
FROM Admins
WHERE TRIM(Admins.AdminID) NOT IN(SELECT TRIM(Servers.AdminID) FROM Servers)

Upvotes: 0

Kritner
Kritner

Reputation: 13765

Your query looks correct to me, but I tend to try to avoid NOT IN when possible, as I usually get better performance out of a query written like:

select adminId, adminfirstname, adminlastname
from admins a
left join servers s on a.adminId = s.adminId
where s.adminId is null

the above is the same idea, just using a join and showing only the results that didn't successfully join.

the other one could be written as:

select serverId, serverName, [server location]
from servers
where adminId is null

Upvotes: 1

Related Questions