How to SELECT * from a table when looking to see if a particular column has two unique values in another column with MySQL?

Question

I have MySQL query I'm trying to run but I can't figure out how to approach it. My example data I'm working with is this

TABLE called Stop
COLUMNS of ID, STATION_ID, NAME_ID
ROWS OF DATA (1, 5, 4), (1, 6, 6), (1, 3, 2), (2, 9, 4), (3, 10, 20), (1, 12, 15), (4, 1, 3)

I'm trying to query for two STATION_ID that exist for one particular ID from the Stop table where the STOP ID has to have both STATION_ID or else it won't return anything

I had SELECT id FROM stop WHERE station_ID = 5 AND station_ID = 6 but this doesn't work. What kind of query parameters do I need to accomplish this?

Answer 1

You seem to want aggregation and a having clause:

select id
from stop
where station_id in (5, 6)
group by id
having count(*) = 2

If there are duplicate (id, station_id), then you need count(distinct station_id) instead of count(*).