SQL Query Not working

Question

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

What do your data sets look like? Or rather, what's your input/output expected to be? — Dan Field
– Dan Field, Commented Jan 20, 2017 at 17:36
Also - it looks like sql-ex.ru has terms in there that specifically ask you not to seek outside help or look for solutions elsewhere.... — Dan Field
– Dan Field, Commented Jan 20, 2017 at 17:40

Ankit Bajpai · Accepted Answer · 2017-01-24 13:13:11Z

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.

Collectives™ on Stack Overflow

SQL Query Not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related