-3

I need to find the users that are on the same flight, the tables that hold the specific information are:

person(person_id, name, dob, address)
passenger (passenger_ID, person_ID)
ticket ( ticket_num(PK), passenger_id(FK), flight_num(FK) )

my approach is to self join ticket table to find the passenger id's that have the same flight number


select t.passenger_id

from tickets t join tickets ti on ti.ticket_num = t.ticket_num

where t.flight_num = ti.flight_num

Will this work??? any suggestions more than welcome! thanks!

juergen d
  • 201,996
  • 37
  • 293
  • 362
Artemis
  • 123
  • 1
  • 8
  • 1
    Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) – philipxy Aug 05 '19 at 07:44
  • 1
    Well, does it work or doesn't it? What is your claim & justification either way? What about example data? What can you say of row pairs that have the same ticket number--does that give you pairs of people you are interested in? What does "Select all passengers on the same flight" even mean?--What data is displayed how? PS You will want person pairs that don't involve the same person. – philipxy Aug 05 '19 at 07:47
  • @philipxy this is more on paper so i dont have a database to test it, i guess select passengers on the same flight means select all the passengers that are on the same flight – Artemis Aug 05 '19 at 07:59
  • Is this a trick question? 'passenger id's that have the same flight number' - there's no suggestion that a specific flight should be selected so effectively it's all flights and everybody ordered by flight number (or group_concatenated if you prefer 1 row per flight) with perhaps an exclusion in the unlikely event of single passenger per flight, – P.Salmon Aug 05 '19 at 08:04
  • Nothing's stopping you from giving example input & output etc. "select all the passengers that are on the same flight" still doesn't mean anything. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. There are many sites to run code online. Google re [so] & sqlfiddle.com. Clarify via edits, not comments. PS When giving a relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. – philipxy Aug 05 '19 at 08:04

1 Answers1

1

It sounds like you are looking for the group functionality in MySQL.

select group_concat(t.passenger_id)
from tickets t
group by t.flight_num
Esben Tind
  • 885
  • 4
  • 14
  • "I need to find the users that are on the same flight" and "to find the passenger id's that have the same flight number" being the key sentences here I think. I took a stab at it. – Esben Tind Aug 05 '19 at 07:54