CODEWITHSUNDEEP
mysqlsql
Mahdi Mohamadi Hidaji
Mahdi Mohamadi Hidaji

Reputation: 33

How can I count the number of childrens (of a value) in MySQL?

I have the following 3 table with nested data:

section table

id      title
1       s1
2       s2
3       s3

term table:

id      id_secion   
1       1
2       1
3       3

visit table:

id      id_term
1       1
2       1
3       1
4       2
5       3
6       3

Which query or way do I need to go to get a result like this:

section     visit_count
1           4
2           0
3           2

data base export is:

 CREATE TABLE IF NOT EXISTS `section` (
      `id` int(10) NOT NULL,
      `title` varchar(5) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;    

    INSERT INTO `section` (`id`, `title`) VALUES
    (1, 's1'),
    (2, 's2'),
    (3, 's3');

    CREATE TABLE IF NOT EXISTS `term` (
      `id` int(11) NOT NULL,
      `id_section` int(10) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO `term` (`id`, `id_section`) VALUES
    (1, 1),
    (2, 1),
    (3, 3);

visit table is:

Upvotes: 2

Views: 31

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

SELECT s.id AS section, COUNT(v.id_term) AS visit_count
FROM section s
LEFT JOIN term t
    ON s.id = t.id_section
LEFT JOIN visit v
    ON t.id = v.id_term
GROUP BY s.id

Follow the link below for a running demo:

SQLFiddle

Upvotes: 2

Related Questions