Hello everyone.

 I have 2 tables:

drivers
(key)DriverID
MotorbikeID

motorbikes
(key)MotorbikeID

I would like to receive all the records in motorbikes table that one of the drivers doesn't have the MotorbikeID.

for example:

drivers

DriverID        MotorbikeID
123                000
234                999
345              (nothing)

motorbikes

000
555
999
777

the search has to return the records 555 and 777

 Thanks!!

SELECT *
FROM Motorbikes
WHERE MotorbikeID NOT IN (SELECT MotorbikeID FROM Drivers)

declare @drivers table
(DriverID varchar(3),MotorbikeID varchar(10) )

INSERT INTO @drivers 
SELECT '123','000' 
UNION ALL SELECT '234','999'
UNION ALL SELECT '345','(nothing)'

declare @motorbikes table(MotorbikeID varchar(10) )
INSERT INTO @motorbikes 
SELECT  '000' 
UNION ALL SELECT  '555'
UNION ALL SELECT  '999'
UNION ALL SELECT  '777'
 
SELECT MotorbikeID FROM (SELECT a.MotorbikeID as MotorbikeID
,b.MotorbikeID bMotorbikeID FROM @motorbikes a
LEFT JOIN @drivers b ON a.MotorbikeID=b.MotorbikeID
WHERE b.MotorbikeID is NULL) t

Limno,

 I'm by no means saying you are incorrect, but I'm just trying to understand your logic here compared to mine. The last statement in his questions said "the search has to return the records 555 and 777". I'm not understanding why you are creating two temp tables and doing a left join if he only wants to motorbike IDs that are not matched with a driver. Maybe I misunderstood so if so I'm hoping you can elaborate.

 Select D.* from Drivers D LEFT Join  Morotbikes M on D.MotorbikeID = M.MotorbikeID where M.MotorbikeID IS NULL - this is faster than SELECT NOT IN.

In SQL Server 2005 and up look at EXCEPT clause as well.

 Limno, just curious why did you introduce derived table here? :)