May 16, 2006 at 10:17 am
Hello all. I have two tables (Equipment and Divisions). The Divisions table contains all valid division codes, and the Equipment table has the equipment numbers along with the corresponding division code. The problem is that many of the equipment were put in through the back-end, therefore no error checking took place. What I need to do is to create a query that will go through the Equipment table and find any Divisions that do not exist in the Divisions table. I have tried various joins, Union, etc, but I can't seem to figure it out.
Any help would be greatly appreciated,
Parallon
May 16, 2006 at 10:24 am
Something of the form
Select DivisonCode
from EquipmentTable
where DivisionCode NOT IN (SELECT DivisionCode
FROM Divisions)
May 16, 2006 at 10:29 am
Thank you so very much. That worked perfectly!!!
Parallon
May 17, 2006 at 8:01 am
Er, a much more efficient query (in case performance is an issue) would be:
SELECT Equip.EquipID, Equip.DivCode
FROM EquipmentTable Equip
LEFT JOIN DivisionTable Div
ON Equip.DivCode = Div.DivCode
WHERE Div.DivCode IS NULL
The LEFT JOIN will give you all the rows from the EquipmentTable, whether or not they have a matching row in the DivisionTable. If there is no matching row, then Div.DivCode will be NULL.
At least nine times out of ten, a LEFT JOIN is faster than a NOT IN query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply