Помогите с выборкой SQL

Question

Есть таблица courses

id	code
123	firstCourse
321	secondCourse

Есть таблица course, которая содержит в одном из столбцов xml структуру с данными курса. В том числе с id каталога, в котором этот курс находится(role_id) при этом course.id=courses.id:

id	data
123	<course...>
321	<course...>

Данный xml имеет следующую структуру, в которой главное для меня это список id каталогов, в которых может содержаться данный курс:

<?xml version="1.0" encoding="utf-8"?>
<course SPXML-FORM="x-local://wtv/wtv_course.xmd">
    ...
    <role_id>0x5C0F8D957FC1671F</role_id>
    <role_id>0x6283A7F92D77546A</role_id>
    <role_id>0x62C84DCD14786E5B</role_id>
    ...
</course>

И наконец есть таблица roles, которая содержит в себе id каталогов и коды:

id	code
546	firstCatalog
845	secondCatalog

Мне необходимо сделать запрос имея только код каталога, который бы мне сформировал бы следующую таблицу:

role_id	role_code	course_id	course_code
546	firstCatalog	123	firstCourse
546	firstCatalog	321	secondCourse
546	firstCatalog	id курса из таблицы courses	code курса из таблицы courses

Другими словами мне необходимо по коду каталога найти все курсы, которые хранятся в этом каталоге через такую довольно сложную для меня цепочку джоинов и подзапросов. Буду рад примерам! Заранее спасибо!

  -- DDL и образец вставки данных, начало
DECLARE @courses TABLE (id  bigint PRIMARY KEY, code VARCHAR(30));
INSERT @courses (id, code) VALUES
(6836290143006832700, 'motivation_ORG'),
(6882720723965317721, 'rise_career'),
(7099728121055442750, 'gift_cards');

    DECLARE @course TABLE (id bigint, [data] XML);
    INSERT @course (id, data) VALUES
    (6836290143006832700, N'<course SPXML-FORM="x-local://wtv/wtv_course.xmd">
                                <role_id>7099728487018208760</role_id>
                                <role_id>7099724487018208760</role_id>
                            </course> '),
    (6882720723965317721, N'<course SPXML-FORM="x-local://wtv/wtv_course.xmd">
                                <role_id>7024413919874268590</role_id>
                                <role_id>7099724487018208760</role_id>
                            </course> '),
    (7099728121055442750, N'<course SPXML-FORM="x-local://wtv/wtv_course.xmd">
                                <role_id>7024413919874268590</role_id>
                                <role_id>7099724487018208760</role_id>
                            </course> ');
    
    DECLARE @roles TABLE (id bigint PRIMARY KEY, code VARCHAR(20));
    INSERT @roles (id, code) VALUES
    (7024413919874268590, 'wel'),
    (7099728487018208760, 'kass');
    -- DDL и образец вставки данных, конец
    
    SELECT * FROM @courses;
    SELECT * FROM @course;
    SELECT * FROM @roles;

Ожидаемый результат: запрос производится по одному из кодов категории ('wel' или 'kass') Запрос должен вывести id, code всех курсов из таблицы courses, относящиеся к категории с кодом 'wel'. Проблема в том, что эта связь существует только через таблицу course, в которой, в data в виде xml хранится перечень id каталогов, в которых пристутсвует курс.

Answer 1

T-SQL

-- DDL и образец вставки данных, начало
DECLARE @courses TABLE (id  INT PRIMARY KEY, code VARCHAR(30));
INSERT @courses (id, code) VALUES
(123, 'firstCourse'),
(321, 'secondCourse');

DECLARE @course TABLE (id INT, [data] XML);
INSERT @course (id, data) VALUES
(123, N'<course SPXML-FORM="x-local://wtv/wtv_course.xmd">
            <role_id>0x5C0F8D957FC1671F</role_id>
            <role_id>0x6283A7F92D77546A</role_id>
            <role_id>0x62C84DCD14786E5B</role_id>
        </course> '),
(321, N'<course SPXML-FORM="x-local://wtv/wtv_course.xmd">
            <role_id>0x5C0F8D957FC1671F</role_id>
            <role_id>0x6283A7F92D77546A</role_id>
            <role_id>0x62C84DCD14786E5B</role_id>
        </course> ');

DECLARE @roles TABLE (id INT PRIMARY KEY, code VARCHAR(20));
INSERT @roles (id, code) VALUES
(546, 'firstCatalog'),
(845, 'secondCatalog');
-- DDL и образец вставки данных, конец

SELECT * FROM @courses;
SELECT * FROM @course;
SELECT * FROM @roles;

БЛОГ НА HUSL

Помогите с выборкой SQL

Ответы (1 шт):