C, PHP, VB, .NET

Досега знаем как да изваждаме информация от повече от една таблица (многотаблични заявки), като се налагаше да използваме връзките между таблиците (join). Извършването на „self join“ означава „да свържем една таблица със самата себе си“. Или казано по друг начин – да направим многотаблична заявка, използвайки една-единствена таблица. Нека демонстрираме с няколко елементарни примера.

Пример 1:

CREATE TABLE clubs(
  id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO clubs(name)
VALUES ('Славия'),
       ('ЦСКА'),
       ('Левски');

Искаме да изкараме списък с всички възможни комбинации от мачове между тези отбори, или по друг начин казано „списък с всеки срещу всеки с разменени домакинства“:

SELECT domakin.name AS dname, gost.name AS gname
FROM clubs AS domakin JOIN clubs AS gost
     ON domakin.id <> gost.id;

dname gname
ЦСКА 	Славия
Левски 	Славия
Славия 	ЦСКА
Левски 	ЦСКА
Славия 	Левски
ЦСКА 	Левски

Ако не се интересуваме от разликите „домакин или гост“ (например правим турнир на един стадион, на който всеки играе срещу всеки по веднъж), то можем да изключим „повторенията“ на мачове по следния начин:

SELECT club1.name AS otbor1, club2.name AS otbor2
FROM clubs AS club1 JOIN clubs AS club2
     ON club1.id > club2.id;

otbor1 otbor2
ЦСКА 	Славия
Левски 	Славия
Левски 	ЦСКА

Self Join по-скоро ще се налага да бъде използван когато имаме зависимост на едни данни в таблица към други данни от същата таблица. Или казано по друг начин – връзка от една колона в таблица с друга колона в същата таблица.

Задача 1: Изкарайте списък, който включва всички възможни мачове (домакинства и гостувания), в които участва отбора с име „Славия“.

Пример 2: Да създадем таблица със стоки, всяка от които стои в дадена категория.

CREATE TABLE inventory(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  parentID INT NULL,
  FOREIGN KEY (parentID) REFERENCES inventory(id),
  name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO inventory(name, id, parentID)
VALUES ("Тениска", 1, NULL),
       ("Суичър", 2, NULL);

INSERT INTO inventory(name, parentID)
VALUES ("Metallica размер L", 1),
       ("Metallica размер M", 1),
       ("Manowar размер L", 1),
       ("Metallica размер L", 2),
       ("Motorhead размер L", 2);

SELECT id, name, parentID FROM inventory;

id name parentID
1 	Тениска 	        NULL
2 	Суичър 	                NULL
4 	Metallica размер L 	1
5 	Metallica размер M 	1
6 	Manowar размер L 	1
7 	Metallica размер L 	2
8 	Motorhead размер L 	2

Тук когато parentID е със стойност NULL данните имат смисъл на „категории (в нашия случай те са две – Тениска и Суичър), а когато има число, то има смисъл на продукт. Виждаме, че някои продукти са с еднакви имена (по-конкретно Metallica размер L), но са в различни категории. Ето как можем да изкараме списък с категориите и съответните продукти в тях:

SELECT parent.name AS tip, child.name AS vid
FROM inventory AS parent JOIN inventory AS child
     ON child.parentID = parent.id;

tip vid
Тениска 	Metallica размер L
Тениска 	Metallica размер M
Тениска 	Manowar размер L
Суичър 		Metallica размер L
Суичър 		Motorhead размер L

Разбира се с такава таблица трябва да се внимава. Лесно е да се създаде стока с невалидна категория (напр. друга стока).

Задача 2: Изкарайте списък само с тениските

Пример 3: Още по-класически е примерът с таблица със служители и техните „мениджъри“.

CREATE TABLE employees(
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  managerID INT NULL,
  FOREIGN KEY (managerID) REFERENCES employees(id)
              ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO employees(id, name, managerID)
VALUES (1, "Иван", NULL),
       (2, "Петър", 1),
       (3, "Александра", 1),
       (4, "Мария", 2),
       (5, "Мариан", 2),
       (6, "Маргарита", 3),
       (7, "Михаил", 3),
       (8, "Ева", 6);

Сега ако искаме да изкараме списък с имената на служителите и имената на техните мениджъри, то можем да направим следната SELF JOIN заявка:

SELECT slujitel.name AS slujitel, manager.name AS reportsTo
FROM employees AS slujitel JOIN employees AS manager
     ON slujitel.managerID = manager.id;

slujitel reportsTo
Петър 		Иван
Александра 	Иван
Мария 		Петър
Мариан 		Петър
Маргарита 	Александра
Михаил 		Александра
Ева 		Маргарита

Разбира се можем да забележим, че „изгубихме“ един служител от списъка и това е Иван, който няма мениджър над себе си (т.е. той може да се приеме за шеф на фирмата). Ако искаме да го включим и него в списъка ще използваме LEFT JOIN:

SELECT slujitel.name AS slujitel, manager.name AS reportsTo
FROM employees AS slujitel LEFT JOIN employees AS manager
     ON slujitel.managerID = manager.id;

slujitel reportsTo
Иван 		NULL
Петър 		Иван
Александра 	Иван
Мария 		Петър
Мариан 		Петър
Маргарита 	Александра
Михаил 		Александра
Ева 		Маргарита

Задача 3 (с повишена трудност): Изкарайте същия списък, но го ограничете само с преките и непреките подчинени на „Александра“. Преки подчинени на Александра са Маргарита и Михаил, а непряк подчинен е Ева (тя е подчинена на Маргарита, която от своя страна на Александра). С други думи очакваме да видим:

slujitel reportsTo
Маргарита 	Александра
Михаил 		Александра
Ева 		Маргарита