CODEWITHSUNDEEP
sqloracle-databasejoingroup-by
Surbhi Jain
Surbhi Jain

Reputation: 369

sql query- group by and then join

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.

Upvotes: 3

Views: 669

Answers (3)

krokodilko
krokodilko

Reputation: 36137

Another way with using analytic functions:

SELECT * FROM (
    SELECT p.*, f.flight_id,
           count(*) OVER (PARTITION BY f.passenger_id ) As number_of_flights
    FROM passenger p
    JOIN flight f
    ON p.passenger_id = f.passenger_id
)
WHERE number_of_flights > 1

Demo: http://sqlfiddle.com/#!4/dab21/11

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32402

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

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this way

Flight id should be multiple so it comma separated of column.

select a.*,b.flightid from passenger_1038299 a
join (select passengerid,Stuff((SELECT ', ' + s.flight_id 
         FROM flight_1038299 l
         where c.passengerid = l.passengerid
         FOR XML PATH('')),1,1,'') flightid from flight_1038299 as c group by c.passengerid having count(c.passengerid)>1) b on a.passengerid=b.passengerid

Upvotes: 0

Related Questions