jeudi 23 juillet 2015

Sqlite SQL query to include empty groups

This might be trivial, but my SQL literacy is limited.

Suppose, you have two tables in a database. Here are the queries to construct and fill them with data:

CREATE TABLE `participiants` (
    `name`  TEXT,
    `age`   INTEGER
);

CREATE TABLE `outcomes` (
    `participiant`  TEXT,
    `score` INTEGER
);

INSERT INTO participiants
VALUES('Alice', 15),
    ('Bob', 37),
    ('Cindy', 56);

INSERT INTO outcomes
VALUES('Alice', 200),
    ('Alice', 250),
    ('Cindy', 180);

The tables with data look like this

participiants

name    age
------------
Alice   15
Bob     37
Cindy   56

outcomes

participiant   score
---------------------
Alice          200
Alice          250
Cindy          180

For every participiants.name I would like to query their MAX(outcomes.score), including the empty group for Bob who is not present in the outcomes table, so the resulting set should look like this:

participiants.name   MAX(outcomes.score)
-----------------------------------
Alice                 250
Bob                   NONE
Cindy                 180

I have tried

SELECT participiants.name, MAX(outcomes.score)
FROM participiants, outcomes
WHERE participiants.name = outcomes.participiant
GROUP BY participiants.name

but, of course, this won't include Bob NONE. What SQL/Sqlite syntax should I look into, to find the solution for this?

Aucun commentaire:

Enregistrer un commentaire