Hi Sergey,

Thanks again

#1 4 sec

#2 0 sec

#3 Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.

I tried working with number 3 but this is my fifth week at SQL Server and I am still treading water.
Thanks again
Jim


>There're a few ways to do that.

-- #1
>SELECT * FROM jw2_studemo_suniq_ident
>  WHERE ident NOT IN (SELECT ident FROM catcboe_students)
>
>-- #2 Correlated subquery
>SELECT * FROM jw2_studemo_suniq_ident v1
>  WHERE NOT Exists (SELECT * FROM catcboe_students cs
>           WHERE cs.ident = v1.ident )
>
>-- #3
>SELECT v1.* FROM jw2_studemo_suniq_ident v1
>  LEFT OUTER JOIN catcboe_students cs
>           WHERE v1.ident = cs.ident  AND cs.ident IS NULL )
>

You should run some tests to find out which one is the fastest. My guess would be that it's #2.
>
>
>>Hi Group
>>
>>I would like to find the records in the jw2_studemo_suniq_ident view, that do not have a matching ident in the catcboe_students table.
>>There are 72 matching records so I should get back 384 records
>>
>>This
>>SELECT * FROM jw2_studemo_suniq_ident
>>Returns 456 records in zero seconds
>>
>>This
>>SELECT * FROM catcboe_students
>>Returns 1801 records in zero seconds
>>
>>
>< snip >