I have tables as follows:
inverter [ id, address, ... ]
string [ id, inverter_id (foreign key), ... ]
I want to select all "inverters", together with the number of "strings" attached to them.
I tried this query here, but it gives me empty result, so how can I do this?
SELECT inverter.*, COUNT(string.*) as string_count
FROM inverter
LEFT JOIN string ON string.inverter_id = inverter.id
ORDER BY address
I am using SQLite3.
Here's a dump of the test tables I have now:
CREATE TABLE `inverter` (`id` INTEGER NULL PRIMARY KEY AUTOINCREMENT, `address` VARCHAR(3) NULL, `comment` VARCHAR(250) NULL);
INSERT INTO "inverter" ("id","address","comment") VALUES ('2','A1','North side');
INSERT INTO "inverter" ("id","address","comment") VALUES ('3','A2','Broken inverter');
INSERT INTO "inverter" ("id","address","comment") VALUES ('4','A3','');
INSERT INTO "inverter" ("id","address","comment") VALUES ('5','A4','South-west corner');
CREATE TABLE `string` (`id` INTEGER NULL PRIMARY KEY AUTOINCREMENT, `address` VARCHAR(3) NULL, `inverter_id` INTEGER NULL, `comment` VARCHAR(250) NULL, FOREIGN KEY (`inverter_id`) REFERENCES `inverters` (`id`) ON DELETE SET NULL);
INSERT INTO "string" ("id","address","inverter_id","comment") VALUES ('1','XX','3','');
INSERT INTO "string" ("id","address","inverter_id","comment") VALUES ('2','XY','3','Ahoj jak se máš');
INSERT INTO "string" ("id","address","inverter_id","comment") VALUES ('3','XZ','4','Moo');
Aucun commentaire:
Enregistrer un commentaire