Tsql select from the corresponding table with the condition AND

Question

I have two related tables:

Table 1

Id ----- 1 2 3 

Table2

 Id Feature -------------- 1 Car 1 Moto 1 Camper 2 Moto 2 Scooter 3 Apple 

I want to select identifiers that have, for example, "Car" and "Moto". Therefore, in the example I want to get only Id = 1.

Answer 1

Use the INTERSECT :

 select id from table2 where feature = 'Car' intersect select id from table2 where feature = 'Moto' 

Answer 2

It:

 WITH features AS ( SELECT feature FROM ( VALUES ('Car'), ('Moto') ) q (feature) ) SELECT * FROM table1 t1 WHERE NOT EXISTS ( SELECT feature FROM features EXCEPT SELECT feature FROM table2 t2 WHERE t2.id = t1.id ) 

or that:

 SELECT * FROM table t1 WHERE ( SELECT COUNT(*) FROM table2 t2 WHERE t2.id = t1.id AND t2.feature IN ('Car', 'Moto') ) = 2 

Which query is more efficient depends on the number of records in both tables and the number of matches.

Answer 3

This choice makes two LEFT OUTER JOIN - table2 (one based on "Car" and the other based on "Moto") and ensures that each JOIN returns a result. DISTINCT guarantees that you will receive each ID only once.

 SELECT DISTINCT t1.id FROM table2 t2 LEFT OUTER JOIN table2 t2_2 ON t2.id = t2_2.id AND t2_2.feature = 'Moto' WHERE t2.feature = 'Car' AND t2_2.id IS NOT NULL 

Edit: The join to table1 has been deleted because it is not really needed.