CODEWITHSUNDEEP
mysqltree
zogby
zogby

Reputation: 482

mysql working with trees

I got this table:

CREATE TABLE 'category' (
    'id' INT(11) NOT NULL AUTO_INCREMENT,
    'parent_category_id' INT(11) NULL DEFAULT NULL,
    'name' VARCHAR(100) NOT NULL,
    PRIMARY KEY ('id'),
    INDEX 'parent_category_id' ('parent_category_id'),
    CONSTRAINT 'category_ibfk_1' FOREIGN KEY ('parent_category_id') REFERENCES 'category' ('id')
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

How do I select categories which have less than 3 subcategories (no depth), and how do I select categories which have no child elements. Thanks!

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

For less than three:

select parent.*
from category parent left outer join
     category child
     on parent.id = child.parent_category_id
group by parent.id
having count(child.id) < 3

For no categories:

select parent.*
from category parent left outer join
     category child
     on parent.id = child.parent_category_id
where child.id is null

Upvotes: 1

Related Questions