MariaDB 10.9

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: mariadb-0:3306
-- Generation Time: Oct 04, 2023 at 07:42 AM
-- Server version: 10.6.16-MariaDB-1:10.6.16+maria~ubu2004-log
-- PHP Version: 8.2.10

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: `mariadb_jemalloc`
--

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

--
-- Table structure for table `order_items`
--

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL,
  `id_order` int(11) NOT NULL,
  `id_product` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `order_items`
--

INSERT INTO `order_items` (`id`, `id_order`, `id_product`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4);

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

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`) VALUES
(1, 'test 1'),
(2, 'test 2'),
(3, 'test 3'),
(4, 'test 4');

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

--
-- Table structure for table `products_sections`
--

CREATE TABLE `products_sections` (
  `id_section` int(11) NOT NULL,
  `id_product` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `products_sections`
--

INSERT INTO `products_sections` (`id_section`, `id_product`) VALUES
(1, 1),
(1, 3),
(2, 1),
(2, 3),
(3, 3),
(4, 3);

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

--
-- Table structure for table `sections`
--

CREATE TABLE `sections` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `priority` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `sections`
--

INSERT INTO `sections` (`id`, `name`, `priority`) VALUES
(1, 'section 1', 1),
(2, 'section 2', 1),
(3, 'section 3', 1),
(4, 'section 4', 1);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id_product` (`id_product`);

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

--
-- Indexes for table `products_sections`
--
ALTER TABLE `products_sections`
  ADD PRIMARY KEY (`id_section`,`id_product`),
  ADD KEY `id_product` (`id_product`);

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `order_items`
--
ALTER TABLE `order_items`
  ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`id_product`) REFERENCES `products` (`id`);

--
-- Constraints for table `products_sections`
--
ALTER TABLE `products_sections`
  ADD CONSTRAINT `products_sections_ibfk_1` FOREIGN KEY (`id_product`) REFERENCES `products` (`id`),
  ADD CONSTRAINT `products_sections_ibfk_2` FOREIGN KEY (`id_section`) REFERENCES `sections` (`id`);
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 */;

SELECT p.id, s.id, s.name
FROM order_items oi
LEFT JOIN products p ON p.id = oi.id_product
LEFT JOIN sections s ON s.id = (
    SELECT sec.id
    FROM sections sec
    LEFT JOIN products_sections ps ON sec.id = ps.id_section
    WHERE ps.id_product = p.id
    ORDER BY sec.priority DESC
    LIMIT 1
)
WHERE oi.id_order = '1';

Records: 4  Duplicates: 0  Warnings: 0

Records: 4  Duplicates: 0  Warnings: 0

Records: 6  Duplicates: 0  Warnings: 0

Records: 4  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

Records: 4  Duplicates: 0  Warnings: 0

Records: 6  Duplicates: 0  Warnings: 0

id	id	name
1	1	section 1
2	null	null
3	1	section 1
4	null	null

version()
10.9.2-MariaDB-1:10.9.2+maria~deb11