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+)