sql query- group by and then join

Question

I have 2 tables as follow:

1)passenger - with passenger_id,passenger_name and passenger_city
2)flight - with flight_id,flight_name and passenger_id.

The question is:

List the passenger details with flight id, who has travelled in more than one flight. (This function will display the passenger details with their flight id's who has travelled in more than one flight.)

I used this query:

select * from passenger_1038299 
where passengerid in(select passengerid from flight_1038299 
                     group by passengerid 
                     having count(passengerid)>1);

but it doesnt give me flight_ids. please tell how to retrieve flight id as well. thanks and sorry for stupid question as new to sql.

Answer 1

Join the flight table to get the passenger's flights

select * from passenger_1038299 p
join flight_1038299 f on f.passenger_id = p.passenger_id
where p.passengerid in(
    select passengerid from flight_1038299 group by passengerid having count(passengerid)>1
);

I like to use exists to check for multiples. With an index on passenger_id it may run faster than the query above.

select * from passenger_1038299 p
join flight_1038299 f on f.passenger_id = p.passenger_id
where exists (
  select 1 from flight_1038299 f2 
  where f2.passenger_id = f.passenger_id
  and f2.flight_id <> f.flight_id
)

Edit

Another way using the count window function:

select * from (
    select *, 
        count() over (partition by p.passenger_id) cnt
    from passenger_1038299 p
    join flight_1038299 f on f.passenger_id = p.passenger_id
) t where cnt > 1