given two tables and a third table relating the two, select only the rows from table A that match a list of table B rows, but don’t match another list

My test schema, data, and query: https://www.db-fiddle.com/f/apQXP7MGfDKPHVw6ucmuNv/1

My query should select only the cars that match ALL of the IN () part features, and NONE of the NOT IN (), and it should only select each matching car once.

In my sample data, when I run the query, I expect to see a single row with two columns, containing the id and name of the only car that matches the list of features in the IN () part of the where clause without matching the list of features in the NOT IN () part of the where clause:

1 | camry

Instead, I get a row containing the name of a car every time that car matches one of the values in the IN () part of the where clause:

1 | camry    | 2 | 1 | 2 | 2 | backup camera
1 | camry    | 3 | 1 | 3 | 3 | sun roof
2 | forester | 4 | 2 | 2 | 2 | backup camera
2 | forester | 5 | 2 | 3 | 3 | sun roof

Answer

If you want one row per car you need aggregation. I think what you want is:

SELECT c.id, c.name
FROM `car` c JOIN
     `bridge_car_features` cf  
      ON car.id = bridge.car_id JOIn
      `features` f  
      ON cf.features_id = f.id 
GROUP BY c.id
HAVING SUM(f.name IN ('backup camera', 'sun roof')) > 0 AND  -- has at least one of these
       SUM(f.name IN ('four wheel drive')) = 0;              -- has none of these

Here is a fiddle.

Note: For the fiddle to work, I made the id be a primary key on cars.