'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 :(

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'SQL - How To Use "CROSS APPLY" In Query

Sources

Related Questions