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