MySql Select records from table with father and sons relationships-CodePudding

Hello and welcome to my question

I have this table:

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
)

As you can see this table represents a company, poorly but it does.

The branch column represents the "child" of a company

I want to find (with a select statement) the name of each company and its parent company, if the company has no parent company then it should display NULL.

I have been thinking of this for some time and I don't even know how to begin. Could you please help me?

CodePudding user response：

You can join the table with itself. You must add aliases to each instance of the table the query includes.

For example, you can do:

select
  c.id, c.businessname,
  p.id, p.businessname
from business c
left join business p on p.id = c.branch

Note: The query uses a LEFT JOIN to ensure you see companies that do not have a parent row. In this case it shows nulls in the place of the parent values.