Problem with SQL query (Inner Join)

flightcaptain · #1 26-Jan-2008, 14:00

Hello to all.I have a problem with an SQL query. I want to link 3 tables and get the results from the database records.My query is as shown below but i cannot receive the records of the database. I also tried Left or Right Joins but the problem there is that although i receive the number of the records that are in table pilot_flight i do not receive all the fields from the other tables. I would appreciate any help. Thank you in advance.

Code:

SELECT
pilot.pilotcallsign,
pilot.pilotname,
pilot.pilotsurname,
flight.departureairport,
flight.destinationairport,
pilot_flight.startdate,
pilot_flight.enddate,
pilot_flight.`status`

FROM

pilot_flight

Inner Join pilot ON pilot_flight.pilotid = pilot.pilotid
Inner Join flight ON flight.flightid = pilot_flight.flightid

TurboPT · #2 29-Jan-2008, 20:33

Could there possibly be some missing IDs?

Using this sample data:

Code:

mysql> select * from pilot;
+---------+------+
| pilotid | whom |
+---------+------+
|       1 | me   |
|       2 | you  |
+---------+------+
2 rows in set (0.00 sec)

mysql> select * from flights;
+----------+--------+
| flightid | planey |
+----------+--------+
|        1 | delta  |
|        2 | fenix  |
+----------+--------+
2 rows in set (0.00 sec)

mysql> select * from pilot_flights;
+----------+---------+
| flightid | pilotid |
+----------+---------+
|        1 |       1 |
|        2 |       1 |
|        1 |       2 |
+----------+---------+
3 rows in set (0.00 sec)

The query you provided (although I shortcut the select portion) worked for me...(unless I'm missed something)

Code:

mysql> SELECT * FROM pilot_flights INNER JOIN pilot ON pilot_flights.pilotid = pilot.pilotid
    -> INNER JOIN flights ON flights.flightid = pilot_flights.flightid;
+----------+---------+---------+------+----------+--------+
| flightid | pilotid | pilotid | whom | flightid | planey |
+----------+---------+---------+------+----------+--------+
|        1 |       1 |       1 | me   |        1 | delta  |
|        2 |       1 |       1 | me   |        2 | fenix  |
|        1 |       2 |       2 | you  |        1 | delta  |
+----------+---------+---------+------+----------+--------+
3 rows in set (0.00 sec)

What exactly, in your particular case, appears to be missing from the dataset?

flightcaptain · #3 30-Jan-2008, 05:34

As you can se from my query i wanted also to show the start date and the end date of the flights and with the query i provided i could not retrieve any single record from the database. The query worked finaly by making the following change. It seems strange to me but it worked. The change is

Code:

FROM 
pilot Inner Join pilot_flight ON pilot.pilotid = pilot_flight.flightid 
Inner Join flight ON flight.flightid = pilot_flight.pilotid

Thanks for the reply

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SQL Trigger problem using MS SQL Server	amirand926	MySQL / PHP Forum	3	30-Jan-2008 15:19
SQL query form	biggieuk	MySQL / PHP Forum	2	28-Nov-2006 09:45
Microsoft SQL Server :: SQL Server message 241, state 1, severity 16	lyuboe	MySQL / PHP Forum	0	11-May-2005 04:31
Easy SQL query	jlee	MySQL / PHP Forum	1	22-Jul-2004 07:15
SQL multiple languages query	samtediou	MySQL / PHP Forum	6	23-Jul-2003 13:09

Problem with SQL query (Inner Join)

Re: problem with SQL query (Inner Join)

Re: Problem with SQL query (Inner Join)