How to have IN and NOT IN at the same time

Question

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

I have a list of people with cars. I need to fulfill a request that will return people with a car type and not 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 example, I want to display only people who have an SUV and do not have a MINI VAN. If we try the CarType IN ("SUV") AND NOT IN ("MINI VAN") sentence, this will not work because the second statement is simply ignored.

To return people who are of type but not of another type at the same time, I tried the following:

• Create a temporary table with an IN clause, say @Contains
• Create a temporary table with a NOT IN clause, say @DoesNotContain
• @Contains, IN
• where , @DoesNotContain.

:

--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

, , MINI VAN.

:

• IN NOT IN ? - SQL, ? (, , SQL, SQL 2005)
• ?
• , IN NOT IN JOIN?
• NOT IN JOIN?

!

, , , , . : (

, , MINI VAN, TRUCK AND NOT SEDAN. .

Answer 1

SELECT Id, Name
FROM Cars 
WHERE CarType = 'SUV'
EXCEPT
SELECT Id, Name
FROM Cars 
WHERE CarType = 'MINI VAN'

SELECT Id, Name
FROM Cars 
WHERE CarType IN ('SUV', 'MINI VAN')
GROUP BY Id, Name
HAVING MIN(CarType) = 'SUV'

, .

SELECT Id,
       NAME
FROM   Cars
WHERE  CarType IN ( 'SUV', 'MINI VAN', 'TRUCK')
GROUP  BY Id,
          NAME
HAVING COUNT(DISTINCT CASE
                        WHEN CarType IN ( 'SUV', 'MINI VAN' ) THEN CarType
                      END) = 2
       AND COUNT(DISTINCT CASE
                            WHEN CarType IN ( 'TRUCK' ) THEN CarType
                          END) = 0 

Answer 2

SQL, 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')

, CarType = 'SUV', CarType = 'MINI VAN'.

Answer 3

LEFT JOIN:

SELECT  a.ID,
        Name
FROM    @temp a
    INNER JOIN @Contains b ON b.ID = a.ID
    LEFT OUTER JOIN @DoesNotContains c ON c.ID = a.ID
WHERE   c.ID IS NULL

INNER JOIN , b.ID a.ID .

LEFT OUTER JOIN NULL, - WHERE c.ID IS NULL a, c.

Answer 4

except - .

where carType in 
(select carType
from cars
where you want to include them
except 
select carType
from cars
where you want to exclude them)

.