CODEWITHSUNDEEP
sql
Bikash Behera
Bikash Behera

Reputation: 444

SQL Query Not working

There are two tables named 

Passenger (ID_psg, name)
Pass_in_trip (trip_no, date, ID_psg, place)

There can be several passengers bearing the same first name and surname (for example, Bruce Willis). My goal is to find the names of different passengers that ever travelled more than once occupying seats with the same number. It's exercise 63 given at sql-ex.ru

My Query at first try was 

SELECT p.name
FROM pass_in_trip  pt LEFT JOIN passenger p
    ON pt.id_psg = p.id_psg
GROUP BY pt.id_psg, pt.place,p.name
HAVING COUNT(*) > 1

I am unable to find out the reason why it did not return the desired result. I know it can be done using subquery but I want to know where I am going wrong. The place column represent the seat number. You can take sql-server as database. Desired result should look something like this

  name
--------
Bruce Willis        
Nikole Kidman       
Mullah Omar

Upvotes: 2

Views: 550

Answers (1)

HLGEM
HLGEM

Reputation: 96572

This will get you the people who are in the pass_in_trip table more than once 

SELECT p.name
FROM pass_in_trip  pt 
INNER JOIN passenger p
    ON pt.id_psg = p.id_psg
GROUP BY p.name
HAVING COUNT(*) > 1

This will get you the people with the same name who have the same seat more than once. Note that this will include two people with different ids who have the same name.

SELECT p.name
FROM pass_in_trip  pt 
INNER JOIN passenger p
    ON pt.id_psg = p.id_psg
GROUP BY pt.place,p.name
HAVING COUNT(*) > 1

This will get you the individuals with different id_psgs who have the same seat (so two different people named Bruce Willis who each have used the same seat multiple times who show up in the results and you would see Brice Willis twice).

SELECT p.name
FROM pass_in_trip  pt 
INNER JOIN passenger p
    ON pt.id_psg = p.id_psg
GROUP BY pt.id_psg,pt.place,p.name
HAVING COUNT(*) > 1

Now when trying to debug code of this nature, it is always best to show all of the items in the group by in the select part of the query even if you intend to remove them later. Had you done this:

SELECT pt.id_psg,pt.place,p.name
FROM pass_in_trip  pt 
INNER JOIN passenger p
    ON pt.id_psg = p.id_psg
GROUP BY pt.id_psg,pt.place,p.name
HAVING COUNT(*) > 1

Then you would have seen why two Bruce Willis's show up and you would have known what the problem was.

Upvotes: 1

Related Questions