samedi 7 mars 2015

Count rows from another table referencing current row by foreign key

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