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