We help IT Professionals succeed at work.

SQL Syntax Help - Omit records if WHERE clause is met

Hi All,

I know the title is very vague as I struggled to provide a descriptive title!

I have the following data:

RecordID      UserID       CarID
1                   2                3
2                   2                1
3                   3                2
4                   2                2
5                   1                1

Could anyone help with syntax to achieve the following... if any UserID that has the carID of 2 then omit that UserID completely. So the records returned would look like this...

RecordID      UserID       CarID
5                   1                1

Many thanks,

Rit
Comment
Watch Question

Try with this querry

select * from <<TableName>> where
UserId not in (select distinct userId from <<TableName>> where CarID = 2)
Top Expert 2011
Commented:
you use a(n)  NOT EXISTS (correlated) subquery for this

where you want to test for an associated (or lack thereof) condition within a subset of the data


like this



select x.*
 from yourtable as x
 where not exists (select userid
                     from yourtable as a
                    where a.userid=x.userid
                      and a.carid=2
                   )

Open in new window

Author

Commented:
Excellent thank both. This makes sense now.

Rit