带有连接和计数的mySQL查询

Question

I have a my sql DB and using php to access it.

My tables look like:

Flights Table Flight ID, Time, Airport

Seats Table Flight ID, Seat Number

I have the following query:

SELECT f.FlightID 'ID', Count(*) 'Seats Booked' FROM flights f, seats s  GROUP BY f.FlightID

I want the out put to be

Flight ID Seats Booked

ID Num seats book

The problem is currently it shows this:

[{"ID":"0","Seats Booked":"37"},{"ID":"1234","Seats Booked":"37"}] (The output is JSON encoded)

When it should be be like 10 and 27 if you follow me? It doesnt seem to be the join correctly.

Please give an explanation of what im doing wrong rather than just an answer.

Thanks!

Answer 1

LmC,

You did not use the clause to make the join. Thats why you got a sum for each grouped instruction.

The correct instruction using your syntax should be something like:

SELECT f.FlightID 'ID', Count(*) 'Seats Booked' 
FROM flights f, seats s  
where s.FlightId = f.FlightId
GROUP BY f.FlightID

Using inner join syntax should be something like:

SELECT f.FlightID 'ID', Count(*) 'Seats Booked' 
FROM flights f
inner join seats s  
on  s.FlightId = f.FlightId
GROUP BY f.FlightID

You can do the something without using the flight table. The flight table is not making any difference because you are returning a group of flightid. And the table seats has flightid. The statement below should be enough. This is true if you are not concerned about flights without booked seats

SELECT s.FlightID 'ID', Count(*) 'Seats Booked' 
FROM seats s  
GROUP BY s.FlightID

If you wanna show flights without booked seats, then you should use left join to flights. The statement below will solve this issue.

SELECT f.FlightID 'ID', Count(*) 'Seats Booked' 
FROM flights f
left join seats s  
on  s.FlightId = f.FlightId
GROUP BY f.FlightID