Assuming three columns in the table: ID, NAME, ROLE
BAD: This will insert or replace all columns with new values for ID=1:
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES (1, 'John Foo', 'CEO');
BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:
INSERT OR REPLACE INTO Employee (id, role)
VALUES (1, 'code monkey');
GOOD: Use SQLite On conflict clause
UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!
UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.
GOOD but tedious: This will update 2 of the columns.
When ID=1 exists, the NAME will be unaffected.
When ID=1 does not exist, the name will be the default (NULL).
INSERT OR REPLACE INTO Employee (id, role, name)
VALUES ( 1,
'code monkey',
(SELECT name FROM Employee WHERE id = 1)
);
This will update 2 of the columns.
When ID=1 exists, the ROLE will be unaffected.
When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES ( 1,
'Susan Bar',
COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
);
What you are doing is fine, you just missed off the group by clause
SELECT p.first_name, p.last_name, COUNT(i.student)
FROM person p, student s LEFT JOIN incident i ON s.id = i.student
WHERE p.id = s.person_id AND s.year_group LIKE "%Year 9%"
GROUP BY p.first_name, p.last_name;
Here's some test data
insert into person values(1, 'student', 'Alice', 'Foo', 'f','1970-01-01');
insert into person values(2, 'student', 'Bob', 'Bar', 'm','1970-01-01');
insert into student values(1,1,0,0,'', 'current','','Year 9');
insert into student values(2,2,0,0,'', 'current','','Year 9');
insert into incident values(1,1,0,0,0,'flu','chicken soup', '2008-01-08');
And here's the output of the query with the group by added to it:
+------------+-----------+------------------+
| first_name | last_name | COUNT(i.student) |
+------------+-----------+------------------+
| Alice | Foo | 1 |
| Bob | Bar | 0 |
+------------+-----------+------------------+
You could further clean up the query by making join clauses from your where clause, and grouping on the person id:
SELECT p.first_name, p.last_name, COUNT(i.student)
FROM person p
INNER JOIN student s ON(p.id = s.person_id)
LEFT JOIN incident i ON(s.id = i.student)
WHERE s.year_group LIKE "%Year 9%"
GROUP BY p.id;
Best Solution
As i understand it : in [a], you are creating a 1:1 relationship, in [b] you are not. They aren't alternatives!
In case [b], if table A would hold invoices, table B could be used for invoicelines whereas in [a] this cannot, since there can only be one record for each record in table A. (Thus only 1 invoiceline per invoice)
So, if you really want an answer, use [b], your [a] construct could be replaced with one table only, and is probably not what you mean. (also since you didn't set a primary key, the same as the FK in a 1:1 relationship)