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.
Advertisement
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;