create database chapter6;
user chapter6;
create table Accounts (
account_id bigint unsigned not null auto_increment,
account_name varchar(1000),
primary key (`account_id`)
);
create table Comments (
comment_id SERIAL PRIMARY KEY,
issue_type VARCHAR(20), -- "Bugs" or "FeatureRequests"
issue_id BIGINT UNSIGNED NOT NULL,
author BIGINT UNSIGNED NOT NULL,
comment_date DATETIME,
comment TEXT,
FOREIGN KEY (author) REFERENCES Accounts(account_id)
);
create table Bugs (
issue_id BIGINT UNSIGNED NOT NULL auto_increment,
primary key(`issue_id`)
);
insert into Bugs value(1234);
create table FeatureRequests (
issue_id BIGINT UNSIGNED NOT NULL auto_increment,
primary key(`issue_id`)
);
insert into FeatureRequests value(2345);
insert into Accounts values(1,'hironomiu');
insert into Comments values(6789,'Bugs',1234,1,now(),'クラッシュします');
insert into Comments values(9876,'FeatureRequests',2345,1,now(),'いいアイディア!');
select * from Bugs as b inner join Comments as c on b.issue_id = c.issue_id and c.issue_type = 'Bugs' where b.issue_id = 1234;
select * from Comments AS c LEFT OUTER JOIN Bugs AS b ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs') LEFT OUTER JOIN FeatureRequests AS f ON (f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests');
explain select * from Comments AS c LEFT OUTER JOIN Bugs AS b ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs') LEFT OUTER JOIN FeatureRequests AS f ON (f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests')\G
select * from Comments AS c
LEFT OUTER JOIN Bugs AS b ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs')
LEFT OUTER JOIN FeatureRequests AS f ON (f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests');