How to join multiple columns from different tables

Question

How can you join multiple tables and display values from tables that referenced further in other tables?

select booking.booking_id,
(select customer.first_name from customer
where customer.customer_id=booking.customer_id) as First_Name,
(select customer.last_name from customer
where customer.customer_id=booking.customer_id) as Last_Name,
(select aircraft_model.aircraft_model from aircraft_model
where aircraft_model_id = (select aircraft.aircraft_model_id from 
aircraft
where aircraft_modle_id = booking.aircraft_id))
from booking;

As you can see I'm trying to pass on the value of aircraft_model_id from the table booking to the aircraft_model table.

The only table connecting the aircraft_model table to the booking table is the aircraft table..

I'm completely new to sql so please do state if there are any errors.

Added in the codes for the table:

CREATE TABLE Booking (
Booking_ID NUMBER(10) NOT NULL PRIMARY KEY,
Company_ID NUMBER(10) NOT NULL REFERENCES 
Penrhyn_Jet_Charter(Company_ID),
Customer_ID NUMBER(10) NOT NULL REFERENCES 
Customer(Customer_ID),
Aircraft_ID NUMBER(10) NOT NULL REFERENCES 
Aircraft(Aircraft_ID),
Assignment_No NUMBER(10) NOT NULL,
Booking_Date DATE DEFAULT SYSDATE,
Charter_Cost NUMBER(14,2) CHECK(Charter_Cost > 0),
Departure_Date DATE NOT NULL,
Departure_Location CHAR(3) NOT NULL,
Arrival_Date DATE NOT NULL,
Arrival_Destination CHAR(3) NOT NULL
);
CREATE TABLE Aircraft_Model (
Aircraft_Model_ID NUMBER(10) NOT NULL PRIMARY KEY,
Aircraft_Model VARCHAR2(255) NOT NULL,
No_Of_Aircrafts NUMBER(10) NOT NULL CHECK (No_Of_Aircrafts > 
0),
Aircraft_Description VARCHAR2(500) 
);
CREATE TABLE Aircraft (
Aircraft_ID NUMBER(10) NOT NULL PRIMARY KEY,
Company_ID NUMBER(10) NOT NULL REFERENCES 
Penrhyn_jet_Charter(Company_ID),
Aircraft_Model_ID NUMBER(10) NOT NULL REFERENCES 
Aircraft_Model(Aircraft_Model_ID),
Seats NUMBER(10) NOT NULL
);

Answer 1

Try this one, use INNER JOIN from booking to aircraft_model

SELECT
 b.booking_id,
 c.firstname AS First_Name,
 c.last_name AS Last_Name,
 am.aircraft_model
FROM booking b
INNER JOIN customer c ON b.customer_id = c.customer_id
INNER JOIN aircraft a ON b.aircraft_id = a.aircraft_model_id
INNER JOIN aircraft_model am ON a.aircraft_model_id = am.aircraft_model_id

• domin thomas: It works, but the values in the column aren't displayed, is that because of a lack of WHERE clause?
• Edgar Allan Bayron: is the customer id exists both in booking and customer tables? is aircraft_id exists in both customer and aircraft tables? because I used INNER JOIN here. If not existing, you might want to try OUTER JOINS instead.
• domin thomas: Customer id exists in both booking and customer tables, however aircraft_id only exists in booking and aircraft table. Aircraft_Model_ID exists in aircraft table and Aircraft_Model table. the column - aircraft_model only exists in Aircraft_Model table.
• domin thomas: I have added in the code for the tables to the question.
• Edgar Allan Bayron: I mean the data itself. If there is a data for example, aircraft_model data which is existing in aircraft but not existing customer (or no relation), INNER JOIN will not work. Use LEFT JOIN instead of INNER JOIN. Play with the JOINS to get the desired results.

Answer 2

The query below works to display the desired output for the question stated above.

SELECT
booking.booking_id,
customer.first_name,
customer.last_name,
aircraft.aircraft_id,
aircraft_model.aircraft_model
FROM
booking
INNER JOIN customer ON customer.customer_id = booking.customer_id
INNER JOIN aircraft ON aircraft.aircraft_id = booking.aircraft_id
INNER JOIN aircraft_model ON aircraft_model.aircraft_model_id = 
aircraft.aircraft_model_id;

• Joe W: Your join on aircraft_model does not look correct as you are using the primary key of the aircraft table to join to the primary key of the aircraft_model table which means the data is not likely matching up unless there is only 1 of each model aircraft and they where inserted into both tables with the same primary key.
• domin thomas: I just realised that, is there a way to make it work then?
• Joe W: The final join should be a. aircraft_model_id = am. aircraft_model_id since the aircraft model join is in the two aircraft tables.
• domin thomas: Updated the answer, that has solved the problem of not all bookings being shown!