Help with query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Help with query

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Help with query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Re: Help with query

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Help with query

Post by VladSun »

Then remove the "HAVING" part ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Re: Help with query

Post by emmbec »

Never mind, it doesn't work, it displays the same counter in both columns, any idea why?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Help with query

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Re: Help with query

Post 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...
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Help with query

Post by VladSun »

Your query, please :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Re: Help with query

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Help with query

Post by VladSun »

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
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Re: Help with query

Post 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. :(
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Help with query

Post 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+)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply