Help with query
Moderator: General Moderators
Help with query
Hi everyone, let me first explain what I have. I am designing a system for a flight company and I have the following information in my tables:
AIRPORTS_TABLE:
AirportName
MX
NZ
JFK
SAN
CDG
CHI
Flights_Table:
Flight_Number | DepartureAirport | ArrivalAirport
1 | MX | JFK
2 | NZ | CDG
3 | CDG | MX
4 | MX | CDG
5 | JFK | SAN
I would like to get in one query the following information:
AirportName | Departures | Arrivals
MX | 2 | 1
NZ | 1 | 0
CDG | 1 | 2
JFK | 1 | 1
SAN | 0 | 1
CHI | 0 | 0
How can I do it? I tried using two queries but I can't get it to work, some times I get the count of the flights right but not all airports (only the ones on the flights table), and some times I get all the airports but not the correct count. Any ideas??? Thanks!
AIRPORTS_TABLE:
AirportName
MX
NZ
JFK
SAN
CDG
CHI
Flights_Table:
Flight_Number | DepartureAirport | ArrivalAirport
1 | MX | JFK
2 | NZ | CDG
3 | CDG | MX
4 | MX | CDG
5 | JFK | SAN
I would like to get in one query the following information:
AirportName | Departures | Arrivals
MX | 2 | 1
NZ | 1 | 0
CDG | 1 | 2
JFK | 1 | 1
SAN | 0 | 1
CHI | 0 | 0
How can I do it? I tried using two queries but I can't get it to work, some times I get the count of the flights right but not all airports (only the ones on the flights table), and some times I get all the airports but not the correct count. Any ideas??? Thanks!
Re: Help with query
[sql]SELECT airports.airportname, count(_arivals.id) AS arivals, count(_departures.id) AS departuresFROM airportsLEFT JOIN flights AS _arivals ON _arivals.ArrivalAirport = airports.airportnameLEFT JOIN Flights AS _departures ON _departures.DepartureAirport = airports.airportnameGROUP BY airports.airportnameHAVING arivals > 0 OR departures > 0[/sql]
I would advice you to use numerical IDs instead of strings.
I would advice you to use numerical IDs instead of strings.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help with query
Thanks a lot, I am using numeric index for the airports, I just did not want to put everything here to confuse anybody. I just made a minnor change in your query, i used >=0 for the HAVING part so those airports that don't have either departure or arrival get displayed.
thanks a lot!
thanks a lot!
Re: Help with query
Then remove the "HAVING" part 
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help with query
Never mind, it doesn't work, it displays the same counter in both columns, any idea why?
Re: Help with query
Nope ...
I tested the query before submitting the post - it works...
Maybe, if you give us more info - your exact query and data samples we would be more helpful
I tested the query before submitting the post - it works...
Maybe, if you give us more info - your exact query and data samples we would be more helpful
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help with query
OK the problem seems to be the fact that I actually have an ID for the airports, so in the flights table I do have the ID of the airport and in the airports table each airport has an ID and a Name, if I use the ID as part of the LEFT JOIN it puts the same counter in both arrival and departure columns. I think it has to do with the counters, I just can't figure it out yet...
Re: Help with query
Your query, please 
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help with query
Code: Select all
SELECT airports.name 'Airport', count( _arrivals.number ) AS 'Arrivals', count( _Departures.number ) AS 'Departures'
FROM airports
LEFT JOIN flights AS _arrivals ON _arrivals.ArrivalAirportId = airports.id
LEFT JOIN flights AS _Departures ON _Departures.DeparturesAirportId = airports.id
GROUP BY airports.nameRe: Help with query
Seems fine to me ... it works with my test DB.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help with query
Code: Select all
CREATE TABLE `airports` (
`name` varchar(111) collate latin1_general_ci NOT NULL,
`id` int(111) NOT NULL
);Code: Select all
CREATE TABLE `flights` (
`number` int(11) NOT NULL,
`DeparturesAirportId` int(111) NOT NULL,
`ArrivalAirportId` int(111) NOT NULL
);Code: Select all
INSERT INTO `airports` (`name`, `id`) VALUES
('MX', 1),
('CDG', 2),
('JFK', 3),
('SAN', 4),
('NZ', 5),
('CHI', 6);Code: Select all
INSERT INTO `flights` (`number`, `DeparturesAirportId`, `ArrivalAirportId`) VALUES
(11, 1, 3),
(22, 5, 2),
(33, 2, 1),
(44, 1, 2),
(55, 3, 2),
(66, 3, 4);Re: Help with query
Sorry, my mistake - my test data couldnt result this issue ... And now I think my query is very wrong 
[sql]SELECT airports.name, (SELECT count(*) FROM flights WHERE flights.DeparturesAirportId = airports.id) AS departures, (SELECT count(*) FROM flights WHERE flights.ArrivalAirportId = airports.id) AS arrivalsFROM airports[/sql]
This *should* work. Maybe it's not the best, but it works
( At least for MySQL 4.1+)
[sql]SELECT airports.name, (SELECT count(*) FROM flights WHERE flights.DeparturesAirportId = airports.id) AS departures, (SELECT count(*) FROM flights WHERE flights.ArrivalAirportId = airports.id) AS arrivalsFROM airports[/sql]
This *should* work. Maybe it's not the best, but it works
( At least for MySQL 4.1+)
There are 10 types of people in this world, those who understand binary and those who don't