Sql statement select

Question

Given tables

station table
id | name
1  | Train A 
2  | Train B 
3  | Train C
4  | Train D

country table

id |  name
1  | country A
2  | country B
3  | country c
4  | country D

trainCountry table

idTrain | idCity
1       | 1
1       | 2
1       | 4
2       | 1
2       | 4
3       | 2
3       | 3
3       | 4
4       | 1
4       | 2
4       | 3
4       | 4

thiere are many trains and many countries , and there 4 trains that will pass to each country. each train has its path for example train A can pass from country A to B to C. train B can only pass from country A to B . I need to get the train that has only the path from country B to country C which are Train C and train D. I tried it by using this sql statement but i didn't get the right record :

select *
from cityTrain ct
where ct.idC = (select id from city c where c.id = 2 OR c.id = 3)

Answer 1

select idTrain from traincountry where idCity=2
intersect
select idTrain from traincountry where idCity=3

Answer 2

One way is to use group by and having:

SELECT idTrain
FROM trainCountry 
WHERE idCity IN (2,3)
GROUP BY idTrain
HAVING COUNT(DISTINCT idCity) = 2

This will get you all the trains that passes in cities 2 and 3.

Another way is to use exists:

SELECT idTrain
FROM trainCountry t0
WHERE idCity = 2 
AND EXISTS
(
    SELECT 1
    FROM trainCountry t1
    WHERE t0.idTrain = t1.idTrain 
    AND idCity = 3 
)