join_example.sql

create temporary table games (id integer, dt DateTime, name varchar(255));
insert into games values
	(1, '2010-01-01 00:00:00', 'Game 1'),
	(2, '2010-01-02 00:00:00', 'Game 2'),
	(3, '2010-01-03 00:00:00', 'Game 3'),
	(4, '2010-01-04 00:00:00', 'Game 4'),
	(5, '2010-01-05 00:00:00', 'Game 5');

CREATE TEMPORARY TABLE games2 SELECT * FROM games;
        
create temporary table people (id integer, name varchar(255));
insert into people values
	(1, 'Sailias'),
	(2, 'Hermy'),
	(3, 'Abstract');
        
create temporary table person_games(person_id integer, game_id integer);
insert into person_games VALUES
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 1),
  (3, 1);

SELECT games2.name, CASE WHEN a.person_name IS NULL THEN 'Needs Game' ELSE 'Has Game' END as game_status
FROM games2
LEFT OUTER JOIN(
    SELECT games.id as game_id, games.name, person_id, people.name as person_name
    FROM games
    LEFT OUTER JOIN person_games ON games.id=person_games.game_id
    LEFT OUTER JOIN people ON people.id=person_games.person_id
    WHERE people.name = 'Sailias') as a ON games2.id = a.game_id;