Page 1 of 1
Help with query
Posted: Wed May 14, 2008 2:48 pm
by emmbec
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!
Re: Help with query
Posted: Wed May 14, 2008 3:05 pm
by VladSun
[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.
Re: Help with query
Posted: Wed May 14, 2008 3:19 pm
by emmbec
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!
Re: Help with query
Posted: Wed May 14, 2008 3:21 pm
by VladSun
Then remove the "HAVING" part

Re: Help with query
Posted: Wed May 14, 2008 3:43 pm
by emmbec
Never mind, it doesn't work, it displays the same counter in both columns, any idea why?
Re: Help with query
Posted: Wed May 14, 2008 3:53 pm
by VladSun
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

Re: Help with query
Posted: Wed May 14, 2008 4:12 pm
by emmbec
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
Posted: Wed May 14, 2008 4:15 pm
by VladSun
Your query, please

Re: Help with query
Posted: Wed May 14, 2008 4:33 pm
by emmbec
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.name
Thanks a lot!
Re: Help with query
Posted: Wed May 14, 2008 4:52 pm
by VladSun
Seems fine to me ... it works with my test DB.
Re: Help with query
Posted: Wed May 14, 2008 5:08 pm
by emmbec
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);
Clearly with that information for example, CDG should only have one departure, and my query returns 3 departures and 3 arrivals... I have no idea what's going on now.

Re: Help with query
Posted: Wed May 14, 2008 5:35 pm
by VladSun
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+)