mysql Fallback to another record if first not exist

Question

I have two tables for creatives and its thumbnails. I want to query in a fallback way like:

Initially, the thumbnail will be displayed for 300x250
IF not available then 336x280, 
IF not available then 300x600, 
IF not available then 728x90, 
IF not available then 160x600, 
IF not available then 320x50, 
IF not available then 300x50, 
IF not available then 468x60

The table structure is like

creatives:
id INT
name VARCHAR
desc TEXT

creative_thumbs:
id
creative_id INT
dimension VARCHAR
img_url VARCHAR

I have made it with multiple joins (one for each dimension but it is a bit slow)

Please add what you've tried so that we don't waste time to come up with what you've already tried. — Nae, Jul 03 '19 at 07:09

score 0 · Answer 1 · answered Jul 03 '19 at 07:17

You can do this with a multiple LEFT JOIN query. It should be like:

SELECT
    c.Name AS CreativeName,
    COALESCE(
        ct1.Dimension,
        ct2.Dimension,
        ct3.Dimension,
        ct4.Dimension,
        ct5.Dimension,
        ct6.Dimension,
        ct7.Dimension,
        ct8.Dimension
        ) AS ThumbnailDimension
FROM creatives c
LEFT JOIN creative_thumbs ct1 ON c.id = ct1.creative_id AND ct1.Dimension = '300x250'
LEFT JOIN creative_thumbs ct2 ON c.id = ct2.creative_id AND ct2.Dimension = '336x280'
LEFT JOIN creative_thumbs ct3 ON c.id = ct3.creative_id AND ct3.Dimension = '300x600'
LEFT JOIN creative_thumbs ct4 ON c.id = ct4.creative_id AND ct4.Dimension = '728x90'
LEFT JOIN creative_thumbs ct5 ON c.id = ct5.creative_id AND ct5.Dimension = '160x600'
LEFT JOIN creative_thumbs ct6 ON c.id = ct6.creative_id AND ct6.Dimension = '320x50'
LEFT JOIN creative_thumbs ct7 ON c.id = ct7.creative_id AND ct7.Dimension = '300x50'
LEFT JOIN creative_thumbs ct8 ON c.id = ct8.creative_id AND ct8.Dimension = '468x60'
;

If this is too slow then try adding index to Dimension column:

CREATE INDEX creative_thumbs_Dimension_index
    on creative_thumbs (Dimension);

mysql Fallback to another record if first not exist

1 Answers1