Clever Geek Handbook

Condition-based cross join group in SQL Server

I have tables of subjects and exams -

enter image description here

The result of my desire is

enter image description here

I have tried a lot, but I cannot join the Exam table to the item table based on the code group.

Is a cross join or any other join possible for these two tables to get the desire result?

+3


source to share


3 answers


You can try this



SELECT A.Code, i.Item, A.Exam
FROM
(
SELECT Code,e.Exam
FROM Item i
CROSS JOIN Exam e
GROUP BY Code,e.Exam
) A
LEFT JOIN Item i ON i.Code= A.Code AND i.Exam = A.Exam
+1


source


I don't see a way to generate the missing data. The calendar table approach would be to cross-link all codes with all exams. Then, on the left, join this table to Item

and select the desired result:

WITH cte AS (
   SELECT *
   FROM (SELECT DISTINCT Code FROM Item) AS C
   CROSS JOIN Exam
)
SELECT
    t1.Code,
    t2.Item,
    t1.Exam
FROM cte t1
LEFT JOIN Item t2
    ON t1.Code = t2.Code AND
       t1.Exam = t2.Exam
ORDER BY
    t1.Code,
    CASE WHEN t2.Item IS NOT NULL THEN 0 ELSE 1 END,
    t1.Exam

Demo here:



Rextester

+1


source


This should lead to what you need. I use UNION to combine CROSS JOIN and element table and then group them to eliminate NULL.

SELECT code, max(item) as item, exam
FROM 
(SELECT distinct i.code, null as item, e.exam FROM exam e cross join item i
union all
SELECT code, item, exam
FROM item) u
group by code, exam
order by code, exam
+1


source






More articles:


All Articles