1

I have a quick question. I'm looking for the concept behind this, not necessarily a ready-to-use code, but if it's not too complex, I don't mind a quick solution. I have an app that manages accounts. These accounts are airports, for example. For each airport, I'd like to show the associated runways (could be 1, could be 10) and the associated contacts (could be 1, could be 10). The accounts are stored in the airports table, contacts are in the contacts table and runways are in the runways table. Structure is as follows:

airports (very fictional data)
+----+--------------+--------------+--------------+-------------+-------------+
| id | airport_name | airport_iata | airport_icao | airport_lat | airport_lon |
+----+--------------+--------------+--------------+-------------+-------------+
|  1 | Schiphol     | SCH          | EHSC         | 123"456'    | 456"789'    |
|  2 | Heathrow     | HEA          | EHHE         | 123"456'    | 456"789'    |
|  3 | John Kennedy | JOH          | EAJK         | 123"456'    | 456"789'    |
+----+--------------+--------------+--------------+-------------+-------------+

contacts (very fictional data)
+----+------------+------------+-----------+------------------+--------------+
| id | airport_id | first_name | last_name |  email_address   | phone_number |
+----+------------+------------+-----------+------------------+--------------+
|  1 |          1 | John       | Ruppert   | john@airport.com |    123456789 |
|  2 |          1 | Mark       | Griffin   | mark@airport.com |    123456789 |
|  3 |          2 | Lia        | Isaac     | lia@gh.nl        |    123456789 |
+----+------------+------------+-----------+------------------+--------------+

runways (very fictional data)
+----+------------+------------+--------+----------+----------------+
| id | airport_id | designator | length | surface  | landing_system |
+----+------------+------------+--------+----------+----------------+
|  1 |          1 | 28R        | 4544m  | tarmac   | ILS (CAT-1)    |
|  2 |          1 | 12L        | 3288m  | concrete | ILS(CAT-3)     |
|  3 |          1 | 14R        | 2644m  | tarmac   | VASI           |
+----+------------+------------+--------+----------+----------------+

What I'm looking for is a query (or multiple queries) that will give me one row with one airport to start with. I'm on a page like airports.php?id=1, so I want to see details of Schiphol. This airport has 2 contacts, so I also want to be able to loop both contacts using a foreach (PHP) and list the contacts. I also want to be able to show 3 runways for this airport and access each column using a foreach.

I tried this:

SELECT airports.*, contacts.*, runways.*
FROM airports
LEFT JOIN contacts ON airports.id = contacts.airport_id
LEFT JOIN runways ON airports.id = runways.airport_id
WHERE airports.id='$id';

My first problem is that it somehow returns double the maximum the number of rows in any table. For example, runways has most rows (3) for airport with id 1, hence I'm getting 6 rows (6 times the airport, 3 times 2 contacts and 2 times 3 runways). Second problem is that by using join (any form), I'm getting a table with 6 rows (correct number of rows wouldn't help too much either) and I can't loop through the results, as each row will contain all airport details, all contact details, all runways.

Can I get an array with 1 element for airport, an array with 2 elements for contacts and an array with 3 elements for runways without using multiple statements? I've been told JOINS is the way to go, but at the moment, it doesn't feel that way. What am I doing wrong?

Thanks for your time!

  • You are using JOINs the proper way, but it is not the solution you are looking for to solve your interface problem. (Because there are no direct relations between CONTACTS and RUNWAYS, it is exploding the number of results). I would go for 3 queries each returning their resultsets, and have the PHP page handle the display to the user. As a bonus, you can get very creative with your user interface mechanics this way, as you don't have to struggle with parsing the resultset. Choosing the right tool for the right problem. – Philippe Aug 22 '18 at 15:20
1

Your JOIN is behaving perfectly normally.

If you have one airports row, and no matching contacts or runways, you would get something like this:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol |   NULL  |  NULL

If you had one airport, one contact, and one runway, you'd get:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R

However, when you've got multiple matching contacts and runways, and there's no connection between a contact and a runway, then each contact will get paired with each runway, as you've seen:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R
 Schiphol | Ruppert |  12L
 Schiphol | Ruppert |  14R
 Schiphol | Griffin |  28R
 Schiphol | Griffin |  12L
 Schiphol | Griffin |  14R

This is called a cross-product. You're getting double the rows you think you want because you're crossing 2 rows with 3 rows (2 * 3 = 6). If you had 7 contacts and 5 runways, you'd be getting 35 rows.

What your best solution is will depend on what you're DBMS is capable of.

Worst case scenario, run three queries: one with the airport results, one with the contacts results, and one with the runways results. Pull all three results sets into PHP. If you're bringing back results for more than one airport, you'd loop through the airports results, and (inside that loop) loop through the other two result sets to pull out the contacts and runways for the current airport.

Many DBMSes provide functions that would let you combine the rows for each airport's contacts or runways into XML or JSON. In that case, you'd have one main query, but you'd have two sub-queries within it to generate the two objects for the other tables. It's be something like (pseudo-code only!):

SELECT airport_name, airport_iata, airport_icao
      ,(SELECT <XML/JSON func>(first_name, last_name, email_address, phone_number)
          FROM contacts
         WHERE airport_id = airports.id
       ) as contacts_obj
      ,(SELECT <XML/JSON func>(designator, length, surface, landing_system)
          FROM runways
         WHERE airport_id = airports.id
       ) as runways_obj
  FROM airports
 WHERE airports.id = 1;

Again, pseudo-code only; you'll need to figure out the details of the function for your DBMS.

Barring an explicit XML or JSON function, most DBMSes have the ability to create string aggregates. This would work much like the function above, except more manually; you'd probably build a string for each row (first_name + '|' + last_name + '|' + ...), and use the aggregate function to combine the multiple contacts into a single string (with a different separator, of course).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.