C, PHP, VB, .NET

Дневникът на Филип Петров


* Self join

Публикувано на 25 март 2012 в раздел Бази от Данни.

Досега знаем как да изваждаме информация от повече от една таблица (многотаблични заявки), като се налагаше да използваме връзките между таблиците (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
Маргарита 	Александра
Михаил 		Александра
Ева 		Маргарита

 



7 коментара


  1. Искам да попитам къде греша при следната заявка. С нея се опитвам да изкарам списък от преките подчинени на "Асен"(в колоната reportTo), и съответните им подчинени(в rabotnici). Въвел съм коректни данни, самостоятелно вложената заявка работи, но като я комбинирам нещо се чупи. Мисля си, че проблема е в IN...

    SELECT employee.name AS Rabotnici, boss.name AS reportTO
    FROM employees AS employee JOIN employees AS boss
    ON employee.managerID = boss.id AND boss.name IN (
    SELECT employees.name
    FROM employees WHERE managerID = ( SELECT employees.id FROM employees
    WHERE employees.name = "Asen" ));

  2. Понеже нямам "Asen" в примерната таблица горе, ще изкарам списък с преките подчинени на "Александра":

    SELECT slujitel.name AS slujitel, manager.name AS reportsTo
    FROM employees AS slujitel
    JOIN employees AS manager ON slujitel.managerID = manager.id
    WHERE manager.name = "Александра";

    Тези вложени SELECT в твоя WHERE са напълно безсмислени.

  3. 1зад:
    SELECT DISTINCT domakin.name AS dname, gost.name AS gname
    FROM clubs AS domakin JOIN clubs AS gost
    ON domakin.id != gost.id
    JOIN clubs ON domakin.name = 'Славия' OR
    gost.name = 'Славия';
    Малко странно изглеждаща заяка, но се получи. Сложих DISTINCT защото резултатите се повтаряха общо 3 пъти. Все още не съм разбрал защо.

    2зад:
    SELECT parent.name AS tip, child.name AS vid
    FROM inventory AS parent JOIN inventory AS child
    ON child.parentID = parent.id
    where parent.id = 1;

  4. Задача 3:
    SELECT emp.name, manager.name
    FROM employees emp JOIN employees manager
    ON emp.managerID=manager.id
    WHERE emp.managerID IN(
    SELECT id FROM employees
    WHERE managerID=3) OR emp.managerID=3;

    Работи за данните, които сме въвели, но как може да се направи да работи, ако има още нива надолу в йерархията т.е. има ли универсално решение, при което да работи без ние да знаем колко са нивата в йерархията?

  5. select slujitel.name as Slujitel, manager.name as Manager
    from employees as slujitel join employees as manager
    on slujitel.manager_id = manager.id
    where slujitel.manager_id >= 3;

    в случая работи, но ако id-тата на мениджърите не са последователни, как би трябвало да запишем заявката ?

Добави коментар

Адресът на електронната поща няма да се публикува


*