mysql: natural join

everyone! I’m currently trying to make a simple table with MySQL that satisfies the below condition.

make a table of ship names, displacement, and numGuns with ones participated ‘Guadalcanal’ battle

3 tables are needed to make this.

I’ve tried several codes but they all failed.

I also wonder if I can use ‘natural join’ redundantly.

Here’s the code I’ve been trying

#1st trial
select distinct ship as name, displacement, numGuns
from Ships natural join Classes
natural join Outcomes
where battle = 'Guadalcanal';

#2nd trial
select distinct ship as Name
from Outcomes natural join Ships
where battle = 'Guadalcanal'
union
select displacement, numGuns
from Classes
where name = Name and ??

I added data input query for the case you might need it.

Big Thanks beforehand!

create table Classes(
  Class varchar(20), type char(20), country varchar(20), numGuns int, bore int, displacement int,
  primary key(Class));

create table Ships(
 name varchar(20), Class varchar(20), launched int,
 primary key(name),
 foreign key(Class) references Classes (Class));

create table Outcomes(
 ship varchar(20), battle varchar(20), result varchar(10),
 foreign key(battle) references Battles(Name),
 foreign key(ship) references Ships(name));

insert into Classes values 
('Bismark', 'bb', 'Germany', 8, 15, 42000),
('Iowa', 'bb', 'USA', 9, 16, 46000),
('Kongo', 'bc', 'Japan', 8, 14, 32000),
('North Carolina', 'bb', 'USA', 9, 16, 37000),
('Renown', 'bc', 'Gt. Britain', 6, 15, 32000),
('Revenge', 'bb', 'Gt. Britain', 8,15, 29000),
('Tennessee', 'bb', 'USA', 12, 14, 32000),
('Yamato', 'bb', 'Japan', 9, 18, 65000);

insert into Ships values
('Prince of Wales', 'Tennessee', 1921),
('Bismark', 'Bismark', 1915),
('Duke of York', 'Kongo', 1914),
('Iowa', 'Iowa', 1943),
('Kirishima', 'Kongo', 1915),
('Kongo', 'Kongo', 1913),
('Fuso', 'Iowa', 1943),
('Yamashiro', 'Yamato', 1942),
('California', 'Iowa', 1943),
('North Carolina', 'North Carolina', 1941),
('Renown', 'Renown', 1916),
('Hood', 'Renown', 1916),
('Scharnhorst', 'Revenge', 1916),
('Revenge', 'Revenge', 1916),
('King George V', 'Revenge', 1916),
('South Dakota', 'Revenge', 1916),
('Tennessee', 'Tennessee', 1920),
('Washington', 'North Carolina', 1941),
('West Virginia', 'Iowa', 1943),
('Yamato', 'Yamato', 1941);

insert into Outcomes values 
('Bismark', 'North Atlantic', 'sunk'),
('California', 'Surigao Strait', 'ok'),
('Duke of York', 'North Cape', 'ok'),
('Duke of York', 'Surigao Strait', 'ok'),
('Fuso', 'Surigao Strait', 'sunk'),
('Hood', 'North Atlantic', 'sunk'),
('King George V', 'North Atlantic', 'ok'),
('Kirishima', 'Guadalcanal', 'sunk'),
('Prince of Wales', 'North Atlantic', 'damaged'),
('Prince of Wales', 'North Cape', 'ok'),
('Scharnhorst', 'North Cape', 'sunk'),
('South Dakota', 'Guadalcanal', 'damaged'),
('Tennessee', 'Surigao Strait', 'sunk'),
('Washington', 'Guadalcanal', 'ok'),
('West Virginia', 'Surigao Strait', 'ok'),
('Yamashiro', 'Surigao Strait', 'ok');

Answer

You want to show data of two tables: ships and classes they are 1:n related, so just join them (inner join on / using). Then you have the condition to only consider ships that partcipated in a particular battle. Conditions belong in the WHERE clause ideally. We look up data in other tables with EXISTS or IN.

select s.name, c.numguns, c.displacement
from ships s
join classes c on c.class = s.class
where s.name in (select ship from outcomes where battle = 'Guadalcanal')
order by s.name;

By putting the condition in the WHERE clause we avoid getting any duplictates. Well, in this case (only one battle) we wouldn’t get duplicates anyway, but consider looking at more than one battle. A join would give us the ships multifold, as a join sais “give me the ships combined with their battles”, while IN and WHERE just say “give me the ships when they participated in at least one of the battles”.

Advertisement

Answer