MySQL - join tables

Question

I need to do a SELECT from the following two tables to the result table:

flight_connection

flight number	departure	arrival
310	NUE	TXL
926	FRA	NUE
312	TXL	NUE

airport

code	description
NUE	Nuremberg
FRA	Frankfurt
TXL	Berlin-Tegel

RESULT TABLE:

code	description	departures	arrivals
NUE	Nuremberg	1	2
FRA	Frankfurt	1	0
TXL	Berlin-Tegel	1	1

This is for counting all departures and arrivals for each airport and if one does not have a departure or arrival then it must be filled with 0 and not NULL.

I have so far gotten a table for either arrival or departure but not together, I've tried with UNION ALL but this just merges departures and arrivals count, I'm a little lost and would appreciate a little help.

Departure

    SELECT DISTINCT code, description, Count(departure) AS departures FROM 
    (
        SELECT code, description, departure FROM airport
            INNER JOIN flight_connection ON departure = code
    ) as tmptbl
    GROUP BY code, description

Arrival

    SELECT DISTINCT code, description, Count(arrival) AS arrivals FROM 
    (
        SELECT code, description, arrivalFROM airport
            INNER JOIN flight_connection ON arrival = code
    ) as tmptbl
    GROUP BY code, description

Did you check the UNION operation documentation? https://www.w3schools.com/sql/sql_union.asp — Daantje, Jun 12 '21 at 12:44

score 1 · Accepted Answer · answered Jun 12 '21 at 12:55

You can do it without a suquery only with two joins

CREATE TABLE flight_connection (
  `flight number` INTEGER,
  `departure` VARCHAR(3),
  `arrival` VARCHAR(3)
);

INSERT INTO flight_connection
  (`flight number`, `departure`, `arrival`)
VALUES
  ('310', 'NUE', 'TXL'),
  ('926', 'FRA', 'NUE'),
  ('312', 'TXL', 'NUE');

CREATE TABLE airport (
  `code` VARCHAR(3),
  `description` VARCHAR(12)
);

INSERT INTO airport
  (`code`, `description`)
VALUES
  ('NUE', 'Nuremberg'),
  ('FRA', 'Frankfurt'),
  ('TXL', 'Berlin-Tegel');

SELECT `code`,`description`,IFNULL(departures,0) as departures,IFNULL(arrivals,0) as arrivals FROM airport a
LEFT JOIN (SELECT COUNT(*) arrivals,`arrival` FROM flight_connection GROUP BY `arrival`) f1 ON a.`code` = f1.`arrival` 
LEFT JOIN (SELECT COUNT(*) departures,`departure` FROM flight_connection GROUP BY `departure`) f2 ON a.`code` = f2.`departure`

code | description  | departures | arrivals
:--- | :----------- | ---------: | -------:
NUE  | Nuremberg    |          1 |        2
FRA  | Frankfurt    |          1 |        0
TXL  | Berlin-Tegel |          1 |        1

db<>fiddle here

Thank you, that did the trick. That is way more elegant than what i was trying to do — eveningnectarine, Jun 12 '21 at 13:04

Dri372 · Answer 2 · 2021-06-12T13:24:08.277

0

mysql use 2 sql like postgresql (see below) replacing FULL OUTER JOIN by LEFT JOIN for the first one and RIGHT JOIN for the second one.

And the use this answer

How to do a FULL OUTER JOIN in MySQL?

postgresql

Union is to 'merge' rows, join for column

SELECT code,desciption,departures, arrivals FROM

( SELECT DISTINCT code, description, Count(departure) AS departures FROM 
    (
        SELECT code, description, departure FROM airport
            INNER JOIN flight_connection ON departure = code
    ) as tmptbl
    GROUP BY code, description ) r1

FULL OUTER JOIN 

   (  SELECT DISTINCT code, description, Count(arrival) AS arrivals FROM 
    (
        SELECT code, description, arrivalFROM airport
            INNER JOIN flight_connection ON arrival = code
    ) as tmptbl
    GROUP BY code, description ) r2

USING (code,description);

You can use coalesce function to chnage Null to 0 if you want.

edited Jun 12 '21 at 13:24

answered Jun 12 '21 at 12:45

Dri372

1,275
3
13

This only gives me all the airports that have arrivals and departures, if an airport only has arrivals or only departures it will not be displayed in this table – eveningnectarine Jun 12 '21 at 12:50
Corrected - FULL OUTER JOIN – Dri372 Jun 12 '21 at 12:53
MySQL has no FULL OUTER JOIN You have to UNION a LEFT and RIGHT JOIN – nbk Jun 12 '21 at 13:03
Right, I'm used to postgresql, let me check how to dot it in mysql ... – Dri372 Jun 12 '21 at 13:14

MySQL - join tables

2 Answers2