Sql Select statement from table with father and son relationships-CodePudding

welcome to my question

I am starting with mysql and I am trying to figure out the select statement and it's uses.

I have the table "business" with these inputs:

It is made like so:

CREATE TABLE IF NOT EXISTS business(
  businessname varchar(250) NOT NULL,
  title varchar(250) NOT NULL,
  registerdate datetime NOT NULL,
  id int NOT NULL,
  city varchar(50) NOT NULL,
  tk varchar(10) NOT NULL,
  number varchar(20) NOT NULL,
  branch int,
  doy_id int NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (branch) REFERENCES business(id)
  ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (doy_id) REFERENCES doy(id_kataxorisis)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET greek COLLATE greek_general_ci;

I want select the id and name of each business and the number of it's branches (if there are no branches I want it to show 0).

ps: I have figured out how to show all the businesses and their "parent" business. I don't know if it's gonna help but I did it this way:

select c.businessname as child, p.businessname as parent
from business c left join business p on p.id = c.branch
order by c.businessname;

The data are:

INSERT INTO business VALUES 
('Vasilopoulos Nikolaos', 'Vasilopoulos','1995-05-20 00:00',26400,'Volos',20004,120,NULL,5),
('Tzatzadaki Maria', 'Tzatzadakis','1992-10-02 00:00',20001,'Patra',24222,58,NULL,2),
('Tzatzadaki Maria', 'Tzatzadakis','1998-08-04 00:00',31200,'Patra',24228,180,20001,3),
('Tzatzadaki Maria', 'Tzatzadakis Ypokatastima 2','1998-08-04 00:00',31201,'Patra',24228,180,31200,3),
('Papadopoulos Ioannis', 'Papadopoulou Mpiskota','2000-01-15 00:00',18777,'Athina',27366,280,NULL,1),
('Kallimani Athanasia', 'Thalassina Kallimanis','2000-03-17 00:00',54874,'Korinthos',22008,71,NULL,4),
('Xristopoulos Ioannis','Chris','2010-05-05 00:00',80000,'Athina',24111,56,NULL,1),
('Kallimani Athanasia', 'Thalassina Kallimanis Kalamatas','2000-03-25 00:00',54875,'Kalamata',25005,23,54874,4),
('Kallimani Athanasia', 'Thalassina Kallimanis Spartis','2000-04-17 00:00',54876,'Sparti',28667,34,54874,4),
('Kallimani Athanasia', 'Thalassina Kallimanis Spartis A tomeas','2000-04-17 00:00',54877,'Sparti',28667,34,54876,4),
('Kallimani Athanasia', 'Thalassina Kallimanis Spartis B tomeas','2000-04-18 00:00',54878,'Sparti',28667,34,54876,4),
('Kallimani Athanasia', 'Thalassina Kallimanis Spartis A tomeas Syskevastirio','2000-04-24 00:00',54879,'Sparti',28667,34,54877,4);

Thank you in advance for your help

CodePudding user response：

Not sure if you wanted the count of branches from more than a level deep, but to just get parent branches and its count of children below would be the query:

SELECT PB.ID,PB.businessname, COUNT(CB.ID) FROM business PB
LEFT JOIN business CB ON CB.branch = PB.ID
GROUP BY PB.ID,PB.businessname