Link to home
Start Free TrialLog in
Avatar of SunnyX
SunnyX

asked on

SQL query bug

I need to write an mySQL query that show me all the airplane passengers that use the same place number only once
 
SELECT DISTINCT pt1.id_psg, 
				           pt1.trip_no, 
				           pt1.place 
FROM pass_in_trip pt1,
	    pass_in_trip pt2 
WHERE ( pt1.place <> pt2.place and 
                 pt1.id_psg =pt2.id_psg and 
                 pt1.trip_no <>pt2.trip_no 
               )

Open in new window


I have some bugs in the m query place help me to resolve it.

id_pst||trip_no||place
1      1123      4c
1      1100      1a
1      1181      1a
3      1145      2c
3      1123      2a
5      1181      1b
5      1182      4b
5      1145      1d
6      1181      1b
6      1123      4b
8      1187      3a
8      1188      3a
8      1181      3c
10      7772      3a
10      7778      2a
10      1187      3d
13      7773      2d
13      7772      1b
14      7772      1c
14      8882      3d
14      7771      4d
14      7771      5d
37      7772      1a
37      8881      1d
37      8882      1a
37      7771      1c

So as you can see in the table passanger 1 is bad ( because it appeared in 1a place twice )
pasanger 8 is bad beucase she use 3a twice

please help me to resolve the bug. Thx in advance
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SunnyX
SunnyX

ASKER

Thx everybody !