MYSQL and Joins

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

User avatar
whitewater4562000
Posts: 66
Joined: Sat Aug 23, 2008 4:22 pm UTC
Location: Scotland
Contact:

MYSQL and Joins

Postby whitewater4562000 » Tue Jul 22, 2014 3:50 pm UTC

Hi,

I am perplexed, now exceedingly warm (not use to hot weather here) and getting very annoyed by sql and my lack of knowledge.
What I am trying to do is select the number and names of passengers relating to a specific booking reference.

Originally I had it counting double the number of passengers as were flying. Turns out it was because it included the joins in counting.
Now I am trying to select more than two passengers as they can be infinite. I can only see doing lots of joins as the solution and that doesn't seem right so
hopefully someone here can direct me.

The code as works currently.

Code: Select all

  SELECT
  *, COUNT(*) AS num_pass
FROM
(SELECT
   p.passenger_name AS first_passenger, p2.passenger_name AS second_passenger
 FROM
   passenger_information AS p
 LEFT JOIN
   passenger_information AS p2 ON p.passenger_id = p2.more_passengers
 LEFT JOIN
   bookings ON p.booking_id = bookings.booking_id
 WHERE
   booking_ref = '00003'
 GROUP BY p.passenger_id) AS temp;


I have tried obvious solutions such as reusing passenger name but that will never change (obvious) and am drawing a blank.

Any help would be greatly received.

NOTE: passenger table is structure like this

Code: Select all

  passenger_id | passenger_name | dob | sex | more_passengers | booking_id |

  Foreign keys are booking_id and more_passengers

User avatar
Xenomortis
Not actually a special flower.
Posts: 1426
Joined: Thu Oct 11, 2012 8:47 am UTC

Re: MYSQL and Joins

Postby Xenomortis » Tue Jul 22, 2014 4:16 pm UTC

whitewater4562000 wrote:What I am trying to do is select the number and names of passengers relating to a specific booking reference.

I don't quite understand what you want your output to be.
Why are you counting?
Why are you left joining a table to itself?

Code: Select all

SELECT passenger_id, passenger_name
   FROM passenger_information INNER JOIN bookings
      ON passenger_information.booking_id = bookings.booking_id
WHERE bookings.booking_ref = '00003'

Does that not get you what you want?
Image

User avatar
whitewater4562000
Posts: 66
Joined: Sat Aug 23, 2008 4:22 pm UTC
Location: Scotland
Contact:

Re: MYSQL and Joins

Postby whitewater4562000 » Wed Jul 23, 2014 8:51 am UTC

The output i am wanting is as follows

Code: Select all

  Num of Passengers |  Passenger Name | Passenger Name |
            2                         Joe Bloggs          Jane Bloggs

  Num of Passengers |  Passenger Name |
           1                    Joe Bloggs

  Num of Passengers |  Passenger Name | Passenger Name |  Passenger Name | ........
            3                        Joe Bloggs          Jane Bloggs              Fred Bloggs


Currently I can only get the first two results and don't know how to achieve the 3 and more without lots of joins, since the number of people flying can be anything

Had inner join before but it wont count the number of passengers correctly, with inner will only every display 1

User avatar
karhell
Posts: 687
Joined: Wed Jun 19, 2013 4:56 pm UTC
Location: Breizh

Re: MYSQL and Joins

Postby karhell » Wed Jul 23, 2014 10:33 am UTC

looks like group_concat() would be a better bet to get the passenger list
AluisioASG wrote:191 years ago, the great D. Pedro I drew his sword and said: "Indent thy code or die!"
lmjb1964 wrote:We're weird but it's okay.
ColletArrow, katakissa, iskinner, thunk, GnomeAnne, Quantized, and any other Blitzers, have fun on your journey!

User avatar
whitewater4562000
Posts: 66
Joined: Sat Aug 23, 2008 4:22 pm UTC
Location: Scotland
Contact:

Re: MYSQL and Joins

Postby whitewater4562000 » Thu Jul 24, 2014 10:43 am UTC

@karhell

Looks like group_concat is what I was needing. Incorporated this into my query and am getting the desired result.

Many Thanks


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 7 guests