Is there a more efficient query to check multiple conditions in a self-join?

The question is to find a state in which all three modes of transportation have taken place in. So, in a table listing different trip details – you want to return a state that has a TripID of a car trip, plane ride, and train ride. Below is my current query

My question – while this answers the question, is there a more succinct way to write it within the confines of being a self-join? I feel a nested subquery would simply overcompensate, but this feels like it answers my current question, but would be messy in larger database searches.

Answer

The question is to find a state in which all three modes of transportation have taken place in.

Just use aggregation:

SELECT T.TripState
FROM TRIP T
WHERE T.TravelMode IN ('Train', 'Plane', 'Car')
GROUP BY T.TripState
HAVING COUNT(DISTINCT T.TravelMode) = 3;