How to have IN and NOT IN at same time

Question

Can someone help me to figure out how is the best way to do this?

I have a list of people with cars. I need to execute a query that will return people that have a type of car and don't have another type at the same time.

Here is my example:

ID          Name       CarType
----------- ---------- ----------
1           John       MINI VAN
1           John       SUV
2           Mary       SUV
2           Mary       SEDAN
3           Paul       SPORT
3           Paul       TRUCK
4           Joe        SUV
4           Joe        MINI VAN

For instance, I want to display only people that have SUV AND DON'T have MINI VAN. If we try the clause CarType IN ('SUV') AND NOT IN ('MINI VAN'), this will not work, because the second statement is just ignored.

In order to return people that have a type but don't have another type at the same time, I tried the following:

• Create a temporary table with the IN clause, let's say @Contains
• Create a temporary table with the NOT IN clause, let's say @DoesNotContain
• Join table with @Contains, this will do the IN clause
• On the where clause, look for IDs that are not in @DoesNotContain table.

The query that I am using is this:

--This is the IN Clause
declare @Contains table(
 ID int not null
)
--This is the NOT IN Clause
declare @DoesNotContains table(
 ID int not null
)

--Select IN
insert into @Contains
SELECT ID from @temp where CarType = 'SUV'

--Select NOT IN
insert into @DoesNotContains
SELECT ID from @temp where CarType = 'MINI VAN'

SELECT 
    a.ID, Name 
FROM
    @temp a 
    INNER JOIN @Contains b on b.ID = a.ID
WHERE 
    a.ID NOT IN (SELECT ID FROM @DoesNotContains)
Group by 
    a.ID, Name

This will return Mary because she has a SUV but does not have a MINI VAN.

Here are my questions:

• Is it possible to execute this IN and NOT IN in the query, without temp tables? Is there something new in SQL that does that? (Sorry, last time I worked with SQL was SQL 2005)
• Should we use temp tables for this?
• If this is the way to go, should I use IN and NOT IN instead of the JOIN?
• How to replace the NOT IN clause with a JOIN?

Thank y'all!

EDIT

I just tested the solutions but unfortunately I did not specify that I need a combination of cartypes. My bad :(

For instance, if I want all users that have SUV and MINI VAN but not TRUCK AND NOT SEDAN. In this case it only John is returned.

Answer 1

This is normally accomplished with a single query in standard SQL, using NOT EXISTS:

SELECT *
FROM mytable AS t1
WHERE CarType = 'SUV' AND 
      NOT EXISTS (SELECT *
                  FROM mytable AS t2 
                  WHERE t1.Name = t2.Name AND t2.CarType = 'MINI VAN')

The above query will select all people having CarType = 'SUV', but do not have CarType = 'MINI VAN'.