title: Inner join 3 table
body: <p>I have 6 table in my database <img src="https://i.stack.imgur.com/EzxmW.jpg" alt="booking_system">. And now I would like to inner join car_space, transaction and sport_facilities. However, I got a problem. </p>
  
  <p>When I use these two sql command respectively, these command also can be run and I can get the result I want.</p>
  
  <pre><code>-- car_space INNER JOIN transaction
  SELECT * FROM car_space INNER JOIN transaction ON car_space.carSpaceId = transaction.carSpaceId ORDER BY transactionId;
  -- sport_facilities INNER JOIN transaction
  SELECT * FROM sport_facilities INNER JOIN transaction ON sport_facilities.sportFacilitiesId = transaction.sportFacilitiesId ORDER BY transactionId;
  </code></pre>
  
  <p>And then, I combine them into one command.</p>
  
  <pre><code>-- Combine But Not Work
  SELECT * FROM transaction
  INNER JOIN car_space ON transaction.carSpaceId = car_space.carSpaceId
  INNER JOIN sport_facilities ON transaction.sportFacilitiesId = sport_facilities.sportFacilitiesId
  ORDER BY transactionId;
  </code></pre>
  
  <p>Although this can be run, there are no result or records was shown.</p>
  
  <p>I want to do is the database can be found the record in which table (car_space / sport_facilities) when I typed a transactionId.
  For example:
  I type <code>WHERE transactionId = 1</code>
  Database can be searched this is from sport_facilities table rather that car_space.</p>
  
  <p>Thank you. Here is some code for reference.</p>
  
  <pre><code>    -- Create a database
  CREATE DATABASE booking_system;
  
  -- Use This database
  USE booking_system;
  
  -- Create smartcart table
  CREATE TABLE card(
      cardId  CHAR(8)     NOT NULL,
      PRIMARY KEY (cardId)
  );
  
  -- Insert some recond to card table
  INSERT INTO card VALUES
      ('4332A0D5'),
      ('637ED500'),
      ('B3895A02'),
      ('E32F3702')
  ;
  
  -- Create user table
  CREATE TABLE user(
      userId      INT(5)          NOT NULL    AUTO_INCREMENT,
      cardNo      CHAR(8)         NOT NULL,
      firstName   VARCHAR(255)    NOT NULL,
      lastName    VARCHAR(255)    NOT NULL,
      sex         CHAR(1)         NOT NULL,
      dob         DATE            NOT NULL,
      hkid        CHAR(8)         NOT NULL,
      email       VARCHAR(255)    NOT NULL,
      telNo       INT(8)          NOT NULL,
      PRIMARY KEY (userId),
      FOREIGN KEY (cardNo) REFERENCES card (cardId) ON DELETE CASCADE,
      UNIQUE (hkid)
  );
  
  -- Alter user table
  ALTER TABLE user AUTO_INCREMENT = 16001;
  
  -- Insert some recond to user table
  INSERT INTO user VALUES
      ('','4332A0D5','Andy','Ding','M','1962-04-20','K5216117','mkding@yahoo.com','98626229'),
      ('','637ED500','Emma','Dai','F','1972-06-15','D5060339','emmadai@yahoo.com.hk','62937453'),
      ('','B3895A02','Brinsley','Au','F','1984-02-24','P8172327','da224@live.hk','91961624'),
      ('','E32F3702','Eric','Fong','M','1990-04-15','Y1129323','ericfong0415@gmail.com','98428731')
  ;
  
  -- Create car space price table
  CREATE TABLE car_space_price(
      spaceNo             INT(2)      NOT NULL    AUTO_INCREMENT,
      price               INT(2)      NOT NULL,
      carSpaceDescription VARCHAR(16),
      CHECK (carSpaceDescription IN ('motorcycles','small vehicles','medium vehicles','large vehicles')),
      PRIMARY KEY (spaceNo)
  );
  
  -- Insert some recond to car space price table
  INSERT INTO car_space_price VALUES
      ('','10','motorcycles'),        -- 1
      ('','10','motorcycles'),        -- 2
      ('','10','motorcycles'),        -- 3
      ('','10','motorcycles'),        -- 4
      ('','10','motorcycles'),        -- 5
      ('','20','small vehicles'),     -- 6
      ('','20','small vehicles'),     -- 7
      ('','20','small vehicles'),     -- 8
      ('','20','small vehicles'),     -- 9
      ('','20','small vehicles'),     -- 10
      ('','40','medium vehicles'),    -- 11
      ('','40','medium vehicles'),    -- 12
      ('','40','medium vehicles'),    -- 13
      ('','80','large vehicles'),     -- 14
      ('','80','large vehicles')      -- 15
  ;
  
  -- Create car space table
  CREATE TABLE car_space(
      carSpaceId  INT(5)      NOT NULL    AUTO_INCREMENT,
      spaceNo     INT(2)      NOT NULL,
      cardNo      VARCHAR(8)  NOT NULL,
      inTime      DATETIME,
      outTime     DATETIME,
      PRIMARY KEY (carSpaceId),
      FOREIGN KEY (spaceNo) REFERENCES car_space_price (spaceNo) ON DELETE CASCADE,
      FOREIGN KEY (cardNo) REFERENCES card (cardId) ON DELETE CASCADE
  );
  
  -- Insert some recond to car space table
  INSERT INTO car_space VALUES
      ('','2','E32F3702','2015-02-23 14:24:18','2015-02-23 17:01:43'),    -- 1    --16004
      ('','6','B3895A02','2016-02-24 11:56:43','2016-02-25 09:21:08'),    -- 2    --16003
      ('','2','E32F3702','2016-02-24 16:42:34','2016-02-24 21:02:45'),    -- 3    --16004
      ('','2','E32F3702','2016-02-25 14:25:32','2016-02-25 17:03:54'),    -- 4    --16004
      ('','6','B3895A02','2016-02-25 17:12:11','2016-02-25 20:58:18'),    -- 5    --16003
      ('','13','637ED500','2016-02-25 19:17:03','2016-02-27 18:05:28'),   -- 6    --16002
      ('','6','B3895A02','2016-02-25 21:14:03','2016-02-25 23:53:28'),    -- 7    --16003
      ('','6','B3895A02','2016-02-26 08:46:23','2016-02-26 17:21:08'),    -- 8    --16003
      ('','2','E32F3702','2016-02-26 14:15:45','2016-02-26 21:01:15'),    -- 9    --16004
      ('','6','B3895A02','2016-02-27 09:42:13','2016-02-27 15:48:45'),    -- 10   --16003
      ('','2','E32F3702','2016-02-27 13:25:45','2016-02-27 15:15:45'),    -- 11   --16004
      ('','6','B3895A02','2016-02-28 10:57:16','2016-02-28 14:41:25'),    -- 12   --16003
      ('','2','E32F3702','2016-02-28 11:47:32','2016-02-28 13:43:15'),    -- 13   --16004
      ('','13','637ED500','2016-02-28 13:04:43','2016-03-02 22:39:46'),   -- 14   --16002
      ('','2','E32F3702','2016-02-28 14:42:34','2016-02-28 21:47:45'),    -- 15   --16004
      ('','6','B3895A02','2016-02-29 08:50:42','2016-02-29 14:28:42'),    -- 16   --16003
      ('','2','E32F3702','2016-02-29 12:12:35','2016-02-29 16:45:28'),    -- 17   --16004
      ('','6','B3895A02','2016-03-01 11:26:43','2016-03-01 14:56:26'),    -- 18   --16003
      ('','6','B3895A02','2016-03-03 13:45:26','2016-03-03 17:54:18')     -- 19   --16003
  ;
  
  -- Create sport facilities price table
  CREATE TABLE sport_facilities_price(
      sportNo             INT(2)      NOT NULL    AUTO_INCREMENT,
      sportType           VARCHAR(10) NOT NULL,
      price               INT(2)      NOT NULL,
      sportDescription    VARCHAR(20),
      PRIMARY KEY (sportNo)
  );
  
  -- Insert some recond to sport facilities price table
  INSERT INTO sport_facilities_price VALUES
      ('','snooker','15','Snooker Room 1'),           -- 1
      ('','snooker','15','Snooker Room 2'),           -- 2
      ('','snooker','15','Snooker Room 3'),           -- 3
      ('','snooker','15','Snooker Room 4'),           -- 4
      ('','table_tennis','15','Table Tennis Room 1'), -- 5
      ('','table_tennis','15','Table Tennis Room 2'), -- 6
      ('','table_tennis','15','Table Tennis Room 3'), -- 7
      ('','table_tennis','15','Table Tennis Room 4'), -- 8
      ('','tennis','30','Tennis Vanue 1'),            -- 9
      ('','tennis','30','Tennis Vanue 2'),            -- 10
      ('','badminton','30','Badminton Vanue 1'),      -- 11
      ('','badminton','30','Badminton Vanue 2'),      -- 12
      ('','basketball','60','Hall')                   -- 13
  ;
  
  -- Create sport facilities table
  CREATE TABLE sport_facilities(
      sportFacilitiesId   INT(5)      NOT NULL    AUTO_INCREMENT,
      sportNo             INT(2)      NOT NULL,
      cardNo              VARCHAR(8)  NOT NULL,
      bookDate            DATE        NOT NULL,
      startTime           TIME        NOT NULL,
      endTime             TIME        NOT NULL,
      PRIMARY KEY (sportFacilitiesId),
      FOREIGN KEY (sportNo) REFERENCES sport_facilities_price (sportNo) ON DELETE CASCADE,
      FOREIGN KEY (cardNo) REFERENCES card (cardId) ON DELETE CASCADE
  );
  
  -- Insert some recond to sport facilities table
  INSERT INTO sport_facilities VALUES
      ('','1','E32F3702','2015-02-23','12:00:00','14:00:00'),     -- 1    --16004
      ('','5','B3895A02','2016-02-23','14:00:00','15:00:00'),     -- 2    --16003
      ('','8','637ED500','2016-02-23','17:00:00','21:00:00'),     -- 3    --16002
      ('','2','E32F3702','2016-02-24','09:00:00','11:00:00'),     -- 4    --16004
      ('','5','4332A0D5','2016-02-24','13:00:00','14:00:00'),     -- 5    --16001
      ('','7','637ED500','2016-02-24','15:00:00','17:00:00'),     -- 6    --16002
      ('','8','B3895A02','2016-02-24','16:00:00','18:00:00'),     -- 7    --16003
      ('','10','4332A0D5','2016-02-25','09:00:00','10:00:00'),    -- 8    --16001
      ('','12','B3895A02','2016-02-25','13:00:00','14:00:00'),    -- 9    --16003
      ('','6','637ED500','2016-02-25','21:00:00','22:00:00'),     -- 10   --16002
      ('','4','637ED500','2016-02-26','11:00:00','13:00:00'),     -- 11   --16002
      ('','8','4332A0D5','2016-02-26','22:00:00','23:00:00'),     -- 12   --16001
      ('','13','B3895A02','2016-02-27','09:00:00','14:00:00'),    -- 13   --16003
      ('','4','637ED500','2016-02-28','12:00:00','14:00:00'),     -- 14   --16002
      ('','3','B3895A02','2016-02-28','14:00:00','15:00:00'),     -- 15   --16003
      ('','4','E32F3702','2016-02-28','17:00:00','19:00:00'),     -- 16   --16004
      ('','5','B3895A02','2016-02-28','21:00:00','22:00:00'),     -- 17   --16003
      ('','2','4332A0D5','2016-02-28','21:00:00','23:00:00'),     -- 18   --16001
      ('','10','E32F3702','2016-02-28','19:00:00','20:00:00'),    -- 19   --16004
      ('','11','B3895A02','2016-02-29','11:00:00','13::00:00'),   -- 20   --16003
      ('','8','E32F3702','2016-02-29','12:00:00','14:00:00'),     -- 21   --16004
      ('','4','4332A0D5','2016-02-29','15:00:00','18:00:00'),     -- 22   --16001
      ('','6','E32F3702','2016-03-01','09:00:00','11:00:00'),     -- 23   --16004
      ('','5','637ED500','2016-03-01','12:00:00','15:00:00'),     -- 24   --16002
      ('','3','B3895A02','2016-03-02','09:00:00','11:00:00'),     -- 25   --16003
      ('','7','4332A0D5','2016-03-02','12:00:00','13:00:00'),     -- 26   --16001
      ('','4','637ED500','2016-03-02','15:00:00','17:00:00'),     -- 27   --16002
      ('','1','E32F3702','2016-03-02','19:00:00','22:00:00'),     -- 28   --16004
      ('','12','4332A0D5','2016-03-03','11:00:00','13:00:00'),    -- 29   --16001
      ('','9','E32F3702','2016-03-03','15:00:00','16:00:00'),     -- 30   --16004
      ('','10','B3895A02','2016-03-03','09:00:00','11:00:00'),    -- 31   --16003
      ('','4','637ED500','2016-03-04','11:00:00','12:00:00'),     -- 32   --16002
      ('','8','E32F3702','2016-03-04','14:00:00','16:00:00'),     -- 33   --16004
      ('','6','B3895A02','2016-03-05','19:00:00','21:00:00'),     -- 34   --16003
      ('','13','E32F3702','2016-03-05','11:00:00','12:00:00'),    -- 35   --16004
      ('','8','637ED500','2016-03-05','14:00:00','15:00:00'),     -- 36   --16002
      ('','4','4332A0D5','2016-03-05','16:00:00','18:00:00'),     -- 37   --16001
      ('','5','E32F3702','2016-03-06','13:00:00','15:00:00'),     -- 38   --16004
      ('','9','B3895A02','2016-03-06','17:00:00','18:00:00'),     -- 39   --16003
      ('','11','4332A0D5','2016-03-07','20:00:00','21::00:00'),   -- 40   --16001
      ('','5','B3895A02','2016-03-07','22:00:00','23:00:00')      -- 41   --16003
  ;
  
  -- Create transaction table
  CREATE TABLE transaction(
      transactionId       INT(5)  UNSIGNED ZEROFILL   NOT NULL    AUTO_INCREMENT,
      userId              INT(5)  NOT NULL,
      carSpaceId          INT(5),
      sportFacilitiesId   INT(5),
      transactionDate     DATE    NOT NULL,
      PRIMARY KEY (transactionId),
      FOREIGN KEY (userId) REFERENCES user (userId) ON DELETE CASCADE,
      FOREIGN KEy (carSpaceId) REFERENCES car_space (carSpaceId) ON DELETE CASCADE,
      FOREIGN KEY (sportFacilitiesId) REFERENCES sport_facilities (sportFacilitiesId) ON DELETE CASCADE
  );
  
  -- Insert some recond to transaction table
  INSERT INTO transaction VALUES
      ('','16004',NULL,'1','2015-02-23'),     -- 1    -- Sport Facilities
      ('','16003',NULL,'5','2015-02-23'),     -- 2    -- Sport Facilities
      ('','16004','2',NULL,'2015-02-23'),     -- 3    -- Car Space
      ('','16002',NULL,'8','2015-02-23'),     -- 4    -- Sport Facilities
      ('','16004',NULL,'2','2016-02-24'),     -- 5    -- Sport Facilities
      ('','16003','6',NULL,'2016-02-24'),     -- 6    -- Car Space
      ('','16001',NULL,'5','2016-02-24'),     -- 7    -- Sport Facilities
      ('','16002',NULL,'7','2016-02-24'),     -- 8    -- Sport Facilities
      ('','16003',NULL,'8','2016-02-24'),     -- 9    -- Sport Facilities
      ('','16004','2',NULL,'2016-02-24'),     -- 10   -- Car Space
      ('','16001',NULL,'10','2016-02-25'),    -- 11   -- Sport Facilities
      ('','16003',NULL,'12','2016-02-25'),    -- 12   -- Sport Facilities
      ('','16004','2',NULL,'2016-02-25'),     -- 13   -- Car Space
      ('','16003','6',NULL,'2016-02-25'),     -- 14   -- Car Space
      ('','16002','13',NULL,'2016-02-25'),    -- 15   -- Car Space
      ('','16002',NULL,'6','2016-02-25'),     -- 16   -- Sport Facilities
      ('','16003','6',NULL,'2016-02-25'),     -- 17   -- Car Space
      ('','16003','6',NULL,'2016-02-26'),     -- 18   -- Car Space
      ('','16002',NULL,'4','2016-02-26'),     -- 19   -- Sport Facilities
      ('','16004','2',NULL,'2016-02-26'),     -- 20   -- Car Space
      ('','16001',NULL,'8','2016-02-26'),     -- 21   -- Sport Facilities
      ('','16003',NULL,'13','2016-02-27'),    -- 22   -- Sport Facilities
      ('','16003','6',NULL,'2016-02-27'),     -- 23   -- Car Space
      ('','16004','2',NULL,'2016-02-27'),     -- 24   -- Car Space
      ('','16003','6',NULL,'2016-02-28'),     -- 25   -- Car Space
      ('','16004','2',NULL,'2016-02-28'),     -- 26   -- Car Space
      ('','16002',NULL,'4','2016-02-28'),     -- 27   -- Sport Facilities
      ('','16002','13',NULL,'2016-02-28'),    -- 28   -- Car Space
      ('','16003',NULL,'3','2016-02-28'),     -- 29   -- Sport Facilities
      ('','16004','2',NULL,'2016-02-28'),     -- 30   -- Car Space
      ('','16004',NULL,'4','2016-02-28'),     -- 31   -- Sport Facilities
      ('','16003',NULL,'5','2016-02-28'),     -- 32   -- Sport Facilities
      ('','16001',NULL,'2','2016-02-28'),     -- 33   -- Sport Facilities
      ('','16004',NULL,'10','2016-02-28'),    -- 34   -- Sport Facilities
      ('','16003','6',NULL,'2016-02-29'),     -- 35   -- Car Space
      ('','16003',NULL,'11','2016-02-29'),    -- 36   -- Sport Facilities
      ('','16004',NULL,'8','2016-02-29'),     -- 37   -- Sport Facilities
      ('','16004','2',NULL,'2016-02-29'),     -- 38   -- Car Space
      ('','16001',NULL,'4','2016-02-29'),     -- 39   -- Sport Facilities
      ('','16004',NULL,'6','2016-03-01'),     -- 40   -- Sport Facilities
      ('','16003','6',NULL,'2016-03-01'),     -- 41   -- Car Space
      ('','16002',NULL,'5','2016-03-01'),     -- 42   -- Sport Facilities
      ('','16003',NULL,'3','2016-03-02'),     -- 43   -- Sport Facilities
      ('','16001',NULL,'7','2016-03-02'),     -- 44   -- Sport Facilities
      ('','16002',NULL,'4','2016-03-02'),     -- 45   -- Sport Facilities
      ('','16004',NULL,'1','2016-03-02'),     -- 46   -- Sport Facilities
      ('','16001',NULL,'12','2016-03-03'),    -- 47   -- Sport Facilities
      ('','16003','6',NULL,'2016-03-03'),     -- 48   -- Car Space
      ('','16004',NULL,'9','2016-03-03'),     -- 49   -- Sport Facilities
      ('','16003',NULL,'10','2016-03-03'),    -- 50   -- Sport Facilities
      ('','16002',NULL,'4','2016-03-04'),     -- 51   -- Sport Facilities
      ('','16004',NULL,'8','2016-03-04'),     -- 52   -- Sport Facilities
      ('','16003',NULL,'6','2016-03-05'),     -- 53   -- Sport Facilities
      ('','16004',NULL,'13','2016-03-05'),    -- 54   -- Sport Facilities
      ('','16002',NULL,'8','2016-03-05'),     -- 55   -- Sport Facilities
      ('','16001',NULL,'4','2016-03-05'),     -- 56   -- Sport Facilities
      ('','16004',NULL,'5','2016-03-06'),     -- 57   -- Sport Facilities
      ('','16003',NULL,'9','2016-03-06'),     -- 58   -- Sport Facilities
      ('','16001',NULL,'11','2016-03-07'),    -- 59   -- Sport Facilities
      ('','16003',NULL,'5','2016-03-07')      -- 60   -- Sport Facilities
  ;
  </code></pre>