SQL query returns only 1 row and what is the correct way to join more than three tables?

I'm working on this hotel reservation system.

I have multiple table for customer, reservation, bill, rooms, room type, agency.

I'm making a receipt.

All these tables are connected but I'm only getting a single row of information instead of three information of receipt.

I'm just a beginner so bear with me if my query is not that complex or experienced enough.

BTW I'm using sqlfiddle.

Here is my query:

SELECT r.bill_ID
     , c.name
     , b.payment_type
     , b.card_number
     , a.agency_name
     , mt.description
     , SUM(x.fee) Amenities
     , mt.room_rate Room
  FROM reservation r
  JOIN bill b
    ON b.bill_ID = r.bill_ID
  JOIN agency a
    ON a.agency_ID = r.agency_ID
  JOIN room m 
    ON m.room_ID = r.room_ID
  JOIN amenities x 
    ON b.amenity_ID = x.amenity_ID
  JOIN customer c
    ON c.cust_ID = r.cust_ID
  JOIN roomType mt
    ON mt.type_ID = m.type_ID;

Here is my table:

CREATE TABLE roomType(
  type_ID varchar(15) PRIMARY KEY,
  description varchar(45),
  room_rate int);
                      
CREATE TABLE amenities(
  amenity_ID varchar(15) PRIMARY KEY,
  amenity_name varchar(45),
  fee decimal(4,1));
  
CREATE TABLE agency(
  agency_ID varchar(15) PRIMARY KEY,
  agency_name varchar(45));
  
CREATE TABLE customer(
  cust_ID varchar(15) PRIMARY KEY,
  name varchar(45),
  address varchar(45),
  contact_no varchar(45),
  email varchar(45),
  agency_ID varchar(15),
  FOREIGN KEY (agency_ID) REFERENCES agency(agency_ID));
  
CREATE TABLE bill(
  bill_ID varchar(15) PRIMARY KEY,
  cust_ID varchar(15),
  amenity_ID varchar(15),
  agency_ID varchar(15),
  payment_type varchar(5),
  card_number int,
  amount int,
  FOREIGN KEY (agency_ID) REFERENCES agency(agency_ID),
  FOREIGN KEY (amenity_ID) REFERENCES amenities(amenity_ID),
  FOREIGN KEY (cust_ID) REFERENCES customer(cust_ID));
  
CREATE TABLE room(
  room_ID varchar(15) PRIMARY KEY,
  type_ID varchar(15),
  room_num int,
  max_guest int,
  availability varchar(15),
  FOREIGN KEY (type_ID) REFERENCES roomType(type_ID));
  
CREATE TABLE reservation(
  rsrvtn_ID varchar(15) PRIMARY KEY,
  bill_ID varchar(15),
  cust_ID varchar(15),
  room_ID varchar(15),
  agency_ID varchar(15),
  check_in date,
  check_out date,
  num_guest int,
  book_status varchar(10),
  FOREIGN KEY (agency_ID) REFERENCES agency(agency_ID),
  FOREIGN KEY (bill_ID) REFERENCES bill(bill_ID),
  FOREIGN KEY (cust_ID) REFERENCES customer(cust_ID),
  FOREIGN KEY (room_ID) REFERENCES room(room_ID));
  
CREATE TABLE administrator(
  admin_ID varchar(15) PRIMARY KEY,
  admin_lvl varchar(15),
  rsrvtn_ID varchar(15),
  bill_ID varchar(15),
  room_ID varchar(15),
  cust_ID varchar(15),
  FOREIGN KEY (cust_ID) REFERENCES customer(cust_ID),
  FOREIGN KEY (rsrvtn_ID) REFERENCES reservation(rsrvtn_ID),
  FOREIGN KEY (bill_ID) REFERENCES bill(bill_ID),
  FOREIGN KEY (room_ID) REFERENCES room(room_ID));

Solution

Your query has a SUM() which makes it an aggregation query. An aggregation query with no GROUP BY returns exactly one row. Or an error.

Your query should be returning an error. Why? The SELECT columns are not consistent with the GROUP BY column. All the unaggregated columns should be in the GROUP BY:

GROUP BY r.bill_ID, c.name, b.payment_type, b.card_number, 
         a.agency_name, mt.description, mt.room_rate

This is common-sense. It is required by most databases. And it is even what is current versions of MySQL support.

Unfortunately, once-upon-a-time, MySQL supported the syntax in your question. Since MySQL 8.0, this is no longer the default behavior. It is controlled by a system setting only_full_group_by, which is explained in the documentation.