set nocount on
create table xAutomobiles (
    auto_id    int identity primary key not null,
    auto_description    varchar(50) not null,
)

create table xAuto_Features (
    Feature_ID int identity primary key not null,
    Feature_type varchar(50) not null,
    Feature_description varchar(50)
)

create table xAutomobiles_joining_table (
    join_ID int identity primary key not null,
    auto_ID int not null references xAutomobiles(auto_ID),
    feature_ID int not null references xAuto_features(feature_ID)
)

select TOP 2000 IDENTITY(int,0,1) AS Nbr INTO Seq FROM Northwind..[Order Details]
CREATE UNIQUE CLUSTERED INDEX Seq_Nbr ON Seq(Nbr)
GO

insert into xAutomobiles values ('Green 1959 Rambler')
insert into xAutomobiles values ('Blue 1971 VW Squareback')
insert into xAutomobiles values ('Blue 1979 Datsun 810')
insert into xAutomobiles values ('Tan 1981 VW Rabbit (gas)')
insert into xAutomobiles values ('Tan 1981 VW Rabbit (diesel)')
insert into xAutomobiles values ('Blue 1989 Honda Civic')

insert into xAuto_Features values ('Rack', 'Rooftop luggage rack')
insert into xAuto_Features values ('AC', 'Air conditioning')
insert into xAuto_Features values ('4dr', 'Four doors')

insert into xAutomobiles_joining_table values (1,3)
insert into xAutomobiles_joining_table values (2,2)
insert into xAutomobiles_joining_table values (3,2)
insert into xAutomobiles_joining_table values (3,3)
insert into xAutomobiles_joining_table values (4,1)
insert into xAutomobiles_joining_table values (4,2)
insert into xAutomobiles_joining_table values (5,1)
insert into xAutomobiles_joining_table values (5,2)
insert into xAutomobiles_joining_table values (5,3)
insert into xAutomobiles_joining_table values (6,1)
insert into xAutomobiles_joining_table values (6,2)
insert into xAutomobiles_joining_table values (6,3)
go

create function TableFromCommaList (@List varchar(200))
returns table as return
select distinct ltrim(rtrim(
    substring(List, Nbr+1, charindex(',', List, Nbr+1) - (Nbr+1)))) Item
from (select replace(rtrim(','+ltrim(@List))+',', ',,', ',') List) OL
    join Seq on substring(List, Nbr, 4000) like ',_%'
    and Nbr between 1 and len(List)
go

select auto_id from xAutomobiles X
--Select the cars
where not exists
--for which there is no feature
  (select 1 from dbo.TableFromCommaList('1, 2')
--in the table of desired features
   where Item not in
--missing from that car's features
      (select Feature_ID from xAutomobiles_joining_table
       where auto_id = X.auto_id))


drop table xAutomobiles_joining_table
drop table xAuto_Features
drop table xAutomobiles
drop function TableFromCommaList
drop table Seq