sqlsql-servercross-apply

SQL - How To Use "CROSS APPLY" In Query


I have two tables (carousels and carousel_images)

carousels table

id small_img_1 small_img_2 large_img_1 large_img_2 img_cta_1 img_cta_2
75 101 102 103 104 cta_1 cta_2
76 201 202 203 204 xxx xxx

carousel_images table

id url alt_tag
101 http_101 text_101
102 http_102 text_102
103 http_103 text_103
104 http_104 text_104

Expected Result I would like to return the following results. Which selects all the images required to build a carousel where carousel.id == 1.

img_id small_img small_img_url small_img_alt large_img large_img_url large_img_alt img_cta
1 101 http_101 text_101 103 http_103 text_103 cta_1
2 102 http_102 text_102 104 http_104 text_104 cta_2

Issue - This is how far I've gone. I can't get results for large_img_1 and large_img_2 induced in my results.

img_id small_img small_img_url small_img_alt img_cta
1 101 http_101 text_101 cta_1
2 102 http_102 text_102 cta_2

This is my query so far.

SELECT
    [img_id],
    [small_img],
    [large_img] [img_cta],
    [carousel_images].[url] AS 'small_img_url',
    [carousel_images].[alt_tag] AS 'small_img_alt'
FROM
    [dbo].[carousels] CROSS APPLY(

        VALUES
            (1, [small_img_1], [img_cta_1]),
            (2, [small_img_2], [img_cta_2])
    ) V(img_id, small_img, img_cta)
LEFT OUTER JOIN [carousel_images] ON [small_img] = [carousel_images].id
WHERE
    [carousel_images] = 75;

Can someone please help? I've been stuck for days.

Also its a legacy system, so that's why the table structure is the way it is. I can't change it unfortunately :(


Solution

  • You are almost there.

    You need to add large_img to the CROSS APPLY. And join to carousel_images twice. Once for the small_img and once for the large_img

    SELECT
        v.[img_id],
        v.[small_img],
        s.[url]       AS small_img_url,
        s.[alt_tag]   AS small_img_alt,
        v.[large_img],
        l.[url]       AS large_img_url,
        l.[alt_tag]   AS large_img_alt,
        v.[img_cta]
    FROM
        [dbo].[carousels] c
        CROSS APPLY
        (    
            VALUES
                (1, [small_img_1], [large_img_1], [img_cta_1]),
                (2, [small_img_2], [large_img_2], [img_cta_2])
        ) v (img_id, small_img, large_img, img_cta)
        LEFT OUTER JOIN [dbo].[carousel_images] s ON v.[small_img] = s.id
        LEFT OUTER JOIN [dbo].[carousel_images] l ON v.[large_img] = l.id