Geek Answers Handbook

Internal connection 3 tables

I have 6 tables in my database booking_system . And now I would like the internal connection of car_space, transactions and sport_facilities. However, I had a problem.

When I use these two sql commands respectively, this command can also be run and I can get the result that I want.

-- 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;

And then I will combine them into one team.

 -- 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;

Although this can be run, no results or records have been shown.

I want the database to find an entry in the table (car_space / sport_facilities) when entering a transaction. For example: I type WHERE transactionId = 1 The database can be found, this is from the sport_facilities table, not car_space.

Thanks. Here is some code for reference.

  -- 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 ;
+6
source share
2 answers

How do you want to concatenate strings?

It looks like all your transactions that reference the car space have a NULL link for sports facilities and vice versa.

Requests are executed in turn, when you execute an INNER JOIN transaction only in car spaces, you get all transaction records with links to car spaces with a car record. All other transactions are filtered out.

Since none of these filtered transaction lines and spaces in the car have references to sports objects (all NULL), when you add INNER JOIN to sports objects, there are no corresponding lines, and again inconsistent lines are filtered out. This leaves you with an empty result set.

In order to get any results from the double INNER JOIN query, the transaction line will have to reference (or link) the vehicle space and the sports facility.

If you want to save all transaction lines with their car space or sports, and the NULLed record no matter what is not referenced, you can change INNER JOINs to LEFT JOINs (just replace the words INNER LEFT in your final query).

0
source

In this case, I believe you would like to use the UNION operator. You do not have transaction IDs that match in both tables, so you are returning 0 rows. LEFT / FULL may also work for you.

 SELECT * FROM car_space INNER JOIN transaction ON car_space.carSpaceId = transaction.carSpaceId ORDER BY transactionId; UNION SELECT * FROM sport_facilities INNER JOIN transaction ON sport_facilities.sportFacilitiesId = transaction.sportFacilitiesId ORDER BY transactionId;
0
source

More articles:


All Articles