CODEWITHSUNDEEP
t-sql
Jack
Jack

Reputation: 15872

Select all Items which don't have a particular value in another table

Table: Car
Columns: CarID, Value

Table: Tyres
Columns: TyreID, CarID, Brand

How can I select all Cars which don't have a corresponding Tyre which is of a certain Brand. In this scenario a car could have a number of different tyres each of which has a different brand.

E.g.

Car1 has 4 tyres, 2 x Brand A, 1 x Brand B, 1 x Brand C.
Car2 has 3 tyres, 1 x Brand A, 2 x Brand B.
Car3 has 4 tyres, 3 x Brand A, 1 x Brand C.

I want to find out the CarID of all vehicles which don't have a single tyre of Brand C. In this instance the result would be Car2.

Example Data:

Car
CarID   Value
--------------
1       Abc
2       Def
3       Geh

Tyre
TyreID  CarID   Brand
----------------
1       1   Brand A
2       1   Brand A
3       1   Brand B
4       1   Brand C
5       2   Brand A
6       2   Brand B
7       2   Brand B
8       3   Brand A
9       3   Brand A
10      3   Brand A
11      3   Brand C

Result
ID      2

Upvotes: 0

Views: 176

Answers (2)

Joe Taras
Joe Taras

Reputation: 15399

Use NOT EXISTS instead NOT IN because IN clause have a limit of 2500 rows

SELECT CarID 
FROM Car
WHERE NOT EXISTS (
  SELECT 'X'
  FROM Tyres
  WHERE Brand='Brand C'
  and Tyres.carId = Car.carId
)

Upvotes: 1

Alexander Sigachov
Alexander Sigachov

Reputation: 1551

You can use NOT IN clause

SELECT CarID 
FROM Car
WHERE CarID NOT IN (
  SELECT CarID 
  FROM Tyres
  WHERE Brand='Brand C'
)

Upvotes: 0

Related Questions