DROP TABLE teams; DROP TABLE tscores
CREATE TABLE teams( teamname VARCHAR(20)); INSERT INTO teams VALUES ('Lions'); INSERT INTO teams VALUES ('Tigers'); INSERT INTO teams VALUES ('Wildcats'); INSERT INTO teams VALUES ('Toads'); CREATE TABLE tscores ( hometeam VARCHAR (20), awayteam VARCHAR (20), homescore INTEGER, awayscore INTEGER ); INSERT INTO tscores VALUES ('Lions', 'Wildcats', 1, 4); INSERT INTO tscores VALUES ('Toads', 'Tigers', 3, 5); INSERT INTO tscores VALUES ('Wildcats', 'Tigers', 0, 0);
In this example we are attempting to obtain the scores for all games played. To get all possible values a CROSS JOIN is used and then conditions as well as a left join are then used to filter the query and make it more accurate.
SELECT home.teamname Home, away.teamname Away, tscores.homescore, tscores.awayscore FROM teams home CROSS JOIN teams away LEFT JOIN tscores ON (home.teamname = tscores.hometeam AND tscores.awayteam = away.teamname) WHERE home.teamname != away.teamname