1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
| SELECT * INTO #temp_a
FROM
(
SELECT 'A' as cle, 1 as val
UNION
SELECT 'A' as cle, 3 as val
UNION
SELECT 'A' as cle, 7 as val
UNION
SELECT 'B' as cle, 2 as val
UNION
SELECT 'B' as cle, 6 as val
UNION
SELECT 'B' as cle, 14 as val
)TAB
SELECT * INTO #temp_b
FROM
(
SELECT 'A' as cle, 2 as r_low, 4 as r_up
UNION
SELECT 'A' as cle, 5 as r_low, 9 as r_up
UNION
SELECT 'A' as cle, 10 as r_low, 15 as r_up
UNION
SELECT 'B' as cle, 1 as r_low, 5 as r_up
UNION
SELECT 'B' as cle, 6 as r_low, 11 as r_up
UNION
SELECT 'B' as cle, 12 as r_low, 15 as r_up
)TAB
-- Résultat attendu
SELECT
a.cle
,a.val
,b.r_low
,b.r_up
FROM #temp_A a
LEFT JOIN #temp_B b
ON a.cle = b.cle
AND a.val BETWEEN b.r_low AND b.r_up
--Objectif, ne pas mettre de between dans la condition de jointure ==>
SELECT
a.cle
,a.val
,b.r_low
,b.r_up
FROM #temp_A a
LEFT JOIN #temp_B b
ON a.cle = b.cle
WHERE a.val BETWEEN b.r_low AND b.r_up
OR .... ? |
Partager