How get from the table all the nodes that have no parent nodes?-CodePudding

How do I get from a table all nodes that have no parent nodes?

I am using a (Closure Table) template.

I need to get all nodes that have no parent nodes.

Consider the fact that each node refers to itself. That is, each node itself is a parent and child node in relation to itself.

It looks something like this.

Below I am showing a test database.

-- phpMyAdmin SQL Dump
-- version 5.1.0
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Oct 15, 2021 at 06:30 PM
-- Server version: 8.0.25
-- PHP Version: 8.0.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = " 00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `closure`
--

-- --------------------------------------------------------

--
-- Table structure for table `category_name`
--

CREATE TABLE `category_name` (
  `id` bigint NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_ru_0900_ai_ci DEFAULT NULL,
  `level` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

--
-- Dumping data for table `category_name`
--

INSERT INTO `category_name` (`id`, `name`, `level`) VALUES
(1, 'Electronics', 0),
(2, 'TV sets', 0),
(3, 'Sensory', 0),
(4, 'Backlit', 0),
(5, 'On wheels', 0),
(6, 'Anti-glare', 0),
(7, 'Super thin', 0),
(8, 'Wall', 0),
(9, 'Telephone', 0),
(10, 'Shell', 0),
(11, 'Button', 0),
(12, 'Sensory', 0),
(13, 'Retractable', 0),
(14, 'Auto', 0),
(15, 'Manual', 0),
(16, 'For home', 0),
(17, 'For bathroom', 0),
(18, 'Rug', 0),
(19, 'With animals', 0),
(20, 'Obstruction', 0),
(21, 'Soap dish ', 0),
(22, 'With holes', 0),
(23, 'Transparent', 0),
(24, 'For kitchen', 0),
(25, 'Pans', 0),
(26, 'With handles', 0),
(27, 'Non-stick', 0),
(28, 'pans', 0),
(29, 'Steam', 0),
(30, 'With lids', 0),
(31, 'Test', 0);

-- --------------------------------------------------------

--
-- Table structure for table `tree_path`
--

CREATE TABLE `tree_path` (
  `children` bigint NOT NULL,
  `parent` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ru_0900_ai_ci;

--
-- Dumping data for table `tree_path`
--

INSERT INTO `tree_path` (`children`, `parent`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 1),
(7, 1),
(8, 1),
(9, 1),
(10, 1),
(11, 1),
(12, 1),
(13, 1),
(14, 1),
(15, 1),
(2, 2),
(3, 2),
(4, 2),
(5, 2),
(6, 2),
(7, 2),
(8, 2),
(3, 3),
(4, 3),
(5, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 6),
(8, 6),
(7, 7),
(8, 8),
(9, 9),
(10, 9),
(11, 9),
(12, 9),
(13, 9),
(14, 9),
(15, 9),
(10, 10),
(11, 10),
(12, 10),
(11, 11),
(12, 12),
(13, 13),
(14, 13),
(15, 13),
(14, 14),
(15, 15),
(16, 16),
(17, 16),
(18, 16),
(19, 16),
(20, 16),
(21, 16),
(22, 16),
(23, 16),
(24, 16),
(25, 16),
(26, 16),
(27, 16),
(28, 16),
(29, 16),
(30, 16),
(17, 17),
(18, 17),
(19, 17),
(20, 17),
(21, 17),
(22, 17),
(23, 17),
(18, 18),
(19, 18),
(20, 18),
(19, 19),
(20, 20),
(21, 21),
(22, 21),
(23, 21),
(22, 22),
(23, 23),
(24, 24),
(25, 24),
(26, 24),
(27, 24),
(28, 24),
(29, 24),
(30, 24),
(25, 25),
(26, 25),
(27, 25),
(26, 26),
(27, 27),
(28, 28),
(29, 28),
(30, 28),
(29, 29),
(30, 30);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `category_name`
--
ALTER TABLE `category_name`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tree_path`
--
ALTER TABLE `tree_path`
  ADD PRIMARY KEY (`children`,`parent`),
  ADD KEY `FK_PARENT` (`parent`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `category_name`
--
ALTER TABLE `category_name`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=33;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tree_path`
--
ALTER TABLE `tree_path`
  ADD CONSTRAINT `FK_CHILDREN` FOREIGN KEY (`children`) REFERENCES `category_name` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `FK_PARENT` FOREIGN KEY (`parent`) REFERENCES `category_name` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Perhaps the answer will be like this.

   ---- -------------- ------- ---------- -------- 
  | id | name         | level | children | parent |
   ---- -------------- ------- ---------- -------- 
  |  1 | Electronics  |     0 |      1   |     1  |
  |  16| For home     |     0 |      16  |     16 |
   ---- -------------- ------- ---------- --------

CodePudding user response：

I'd use NOT EXISTS () to check that there are no parent rows in the tree_path table (other than itself).

SELECT
  *
FROM
  category_name   c
WHERE
  NOT EXISTS (
    SELECT *
      FROM tree_path   t
     WHERE t.parent != c.id
       AND t.children = c.id
  )
  AND EXISTS (
    SELECT *
      FROM tree_path   t
     WHERE t.parent = c.id
       AND t.children = c.id
  )

Demo : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=55698aa8b51f99d42db63feda15148a7

(Edited to exclude categories that are not in tree_path)