I am trying to join two tables but I am having some trouble.
This is the main table,
( 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 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
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
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).