I can not figure out how to implement a sql query in such a way as to calculate the corresponding fields in the bundles of two tables.

There is the following table structure:

 CREATE TABLE `tbl1` ( `id` INT(11) UNSIGNED NOT NULL auto_increment, `title` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8; CREATE TABLE `tbl2` ( `id` INT(11) UNSIGNED NOT NULL auto_increment, `tbl1_id` INT(11) UNSIGNED NOT NULL, INDEX (`tbl1_id`), PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8;

It is necessary to count the number of all rows of the first table and exactly those that are not in the second. The first table is related to the second in the following fields: tbl1 . id and tbl2 . tbl1_id That is, if the 20 id first table is not in the second table, then the result should be 20. How can I calculate?

    1 answer 1

    Use left join . Connect the tables, see which rows did not connect, count their number:

     select count(t1.id) from tbl1 t1 left join tbl2 t2 on t1.id = t2.tl1_id where t2.id is null
    • See which fields did not connect - probably not fields, but records? - Akina
    • @Akina I think this answer will count and repeating columns. You need to either add GROUP BY t1.id , or select count(DISTINCT(t1.id)) do you think? - Vanya Avchyan
    • @VanyaAvchyan did not say anything about the vehicle data if there are repetitions or not. With such success, you can do a check for null t1 fields and everything else. - Denis
    • @Akina yes, probably not fields, but records, corrected. - Denis