i have 6 table in database 
. , inner join car_space, transaction , sport_facilities. however, got problem.
when use these 2 sql command respectively, these command can run , can result want.
-- car_space inner join transaction select * car_space inner join transaction on car_space.carspaceid = transaction.carspaceid order transactionid; -- sport_facilities inner join transaction select * sport_facilities inner join transaction on sport_facilities.sportfacilitiesid = transaction.sportfacilitiesid order transactionid; and then, combine them 1 command.
-- combine not work select * transaction inner join car_space on transaction.carspaceid = car_space.carspaceid inner join sport_facilities on transaction.sportfacilitiesid = sport_facilities.sportfacilitiesid order transactionid; although can run, there no result or records shown.
i want database can found record in table (car_space / sport_facilities) when typed transactionid. example: type where transactionid = 1 database can searched sport_facilities table rather car_space.
thank you. here code reference.
-- create database create database booking_system; -- use database use booking_system; -- create smartcart table create table card( cardid char(8) not null, primary key (cardid) ); -- insert recond card table insert 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 recond user table insert 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 recond car space price table insert 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 recond car space table insert 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 recond sport facilities price table insert 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 recond sport facilities table insert 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 recond transaction table insert 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 ;
how wish combine rows?
looks transactions reference car space have null sports facility reference , vice versa.
queries done row-by-row, when inner join transaction car spaces, transaction records car space references car space record. other transactions filtered out.
as none of these filtered transaction + car space rows have sports facility references (all null), when add inner join sports facilities there no matching rows , again non-matching rows filtered out. leaves empty results set.
to results double inner join query, transaction row have reference (or link) car space , sports facility.
if want keep transaction rows either car space or sports facility , nulled out record whichever not referenced, change inner joins left joins (just replace words inner left in final query).
Comments
Post a Comment