Conditional filter for MySQL query

Question

I've got a query that looks like this:

select a.title, is.filename
  from articles a
  join article_images ai on ai.article_id = a.article_id
  join images i on i.image_id = ai.image_id
  join image_sizes is on is.image_id = i.image_id
 where is.size_name = '96x96';

So there is an N:N relationship between articles and images, and an N:1 relationship between images and image sizes. This selects the 96x96 image for each article.

Now I want to change this so it selects the 96x96 image, unless the article_id is 42, in which case it selects a different size. Is there a way to do that in one query?

Answer 1

Change to this:

where (a.article_id <> 42 AND is.size_name = '96x96')
   OR (a.article_id = 42 AND is.size_name = 'something different')

Answer 2

I think you should separate your query into two queries: query for 96x96 and query for article_id=42. Then you should UNION this two results. It will also help you to keep your queries clean and readable.

Answer 3

(select a.title, is.filename
  from articles a
  join article_images ai on ai.article_id = a.article_id
  join images i on i.image_id = ai.image_id
  join image_sizes is on is.image_id = i.image_id
 where is.size_name = '96x96' and a.article_id <> 42)
union all
(select a.title, is.filename
  from articles a
  join article_images ai on ai.article_id = a.article_id
  join images i on i.image_id = ai.image_id
  join image_sizes is on is.image_id = i.image_id
 where is.size_name = 'Some other size' and a.article_id = 42)

You will need to replace 'some other size' with whatever the other size is, or use a SELECT TOP 1 and a GROUP BY in the second query if you don't know the size.