Learning machine learning? Try my machine learning flashcards or Machine Learning with Python Cookbook.
Join Multiple Table
Create Table Of Elves
-- Create table called elves
CREATE TABLE elves (
-- string variable
name varchar(255),
-- integer variable
age int,
-- string variable
race varchar(255)
)
Create Table Of Weapons
-- Create table called weapons
CREATE TABLE weapons (
-- string variable
name varchar(255),
-- string variable
weapon varchar(255),
-- integer variable
weight int
)
Create Table Of Armor
-- Create table called armor
CREATE TABLE armor (
-- string variable
name varchar(255),
-- string variable
body varchar(255),
-- string variable
helm varchar(255)
)
Insert Rows Into Elf Table
INSERT INTO elves (name, age, race)
VALUES ('Dallar Woodfoot', 25, 'Elf'),
('Cordin Garner', 29, 'Elf'),
('Keat Knigh', 24, 'Elf'),
('Colbat Nalor', 124, 'Elf')
Insert Rows Into Weapon Table
INSERT INTO weapons (name, weapon, weight)
VALUES ('Dallar Woodfoot','Axe', 2),
('Cordin Garner', 'Halberd', 3),
('Keat Knigh', 'Dagger', 4),
('Colbat Nalor', 'Dagger', 5)
Insert Rows Into Armor Table
INSERT INTO armor (name, body, helm)
VALUES ('Dallar Woodfoot', 'Leather', 'Leather'),
('Cordin Garner', 'Leather', NULL),
('Keat Knigh', 'Plate', 'Plate'),
('Colbat Nalor', 'Plate', 'Plate')
Join All Tables
-- All rows from table
SELECT elves.name, age, weapon, weight, body, helm FROM elves
-- Join with weapon table using name as key
LEFT JOIN weapons ON (elves.name = weapons.name)
-- Join with armor table using name as key
LEFT JOIN armor ON (elves.name = armor.name)
name | age | weapon | weight | body | helm |
---|---|---|---|---|---|
Dallar Woodfoot | 25 | Axe | 2 | Leather | Leather |
Cordin Garner | 29 | Halberd | 3 | Leather | NULL |
Keat Knigh | 24 | Dagger | 4 | Plate | Plate |
Colbat Nalor | 124 | Dagger | 5 | Plate | Plate |