I have two tables (carousels and carousel_images)

carousels table

carousel_images table

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

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.

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

CodePudding user response：

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