C. Ovidiu C. Ovidiu - 3 months ago 14
MySQL Question

MySQL JOINS: Select one row from child table is condition is met, otherwise NULL

I am trying to join two tables but I am having some trouble.
This is the main table,

packs


This is the main table

And here is the second table,
media

enter image description here

The
media
is for storing images of
packs
. Each pack can have 0 or more images. When the pack has images, one of them will have the
is_default
field set to
1
to indicate the main image to show.

I want to get all pack and one image for each. If there are no images, then just a simple
NULL
, otherwise get the image that is
is_default
.

Here is my query.

SELECT
pack.*,
( SELECT media.src FROM packs pack LEFT JOIN packs_media media ON pack.id = media.pack_id WHERE media.is_default = 1 GROUP BY media.id LIMIT 1 ) AS image
FROM packs pack
LEFT JOIN packs_media ON media.pack_id = pack.id
GROUP BY pack.id
ORDER BY pack.id DESC


I have a total of three packs, and only one of them has some images.
The query returns 3 results. The problem is that all three results have the same
image
, when only one should and the other two should have the
image
field null/empty.

Is there any way of doing this with one query only ? I want to avoid querying the
media
table in a loop.

Thank you

Answer

I had a similar problem lately, and it has a really-really sweet solution, to be honest. At first glance, an obvious try would be adding the WHERE condition is_default = 1. However, if we do this, where there are no images, the condition won't be met, meaning where it should return NULL, it just skips that row.

However, if you add this condition to the LEFT JOIN part, as I did, it works like a charm. It's beacause if the LEFT JOIN conditions met, it returns the image, and if it doesn't, it returns NULL, per the definition of LEFT JOIN.

So the result should be:

SELECT pack.*, media.src
FROM packs pack 
LEFT JOIN media
ON media.pack_id = pack.id
AND media.is_default = 1

And I think you shouldn't add GROUP BY it just messes things up.

If you want to solve your problem even when there's no is_default... well, that is a harder nut to crack, but I think I have a solution which won't dwell into long lines of conditions and subqueries. Sadly, I have the feeling that this problem can't be solved without a subquery. My idea was not to choose the item whose media.is_default equals to 1, rather which has the highest media.is_default. As follows:

SELECT pack.*, media.src
FROM packs pack 
LEFT JOIN media
ON media.pack_id = pack.id
AND media.id = 
   (SELECT m.id FROM media m
    WHERE m.pack_id = media.pack_id
    ORDER BY m.is_default DESC
    LIMIT 1
   )

EXPLANATION: This is how it works: we order all by is_default in a descending order, and refer back to the outer query to use only the current pack_id (I hope it works this way.) If there is a 1, it obviously will be the first item, otherwise it will be a random item, most likely the first in order of ID. And you just take that element and use it. I cannot guarantee if it works at all. Try using IN instead of = as a possible bug fix.

However, I would NOT advise this solution. I'd rather change the previous logic to avoid this problem (e.g. when the user adds a media to an empty pack, is_default = 1 on that picture).