[quote=“Lemon_Juice, post:20, topic:197054, full:true”]
well, I never said anything about not having to join!
[/quote]quite right, you said the join would involve more columns, which is kinda just “less noise and hassle” again
it was me who mentioned you’d have to do extra joins
you’re using an unnecessarily complex query which makes it harder to write than it should be
try this –
SELECT label.name
, GROUP_CONCAT(colour.colour) AS colours
FROM user_has_photo_has_label AS upl
INNER
JOIN label
ON label.id = upl.label_id
INNER
JOIN user_has_photo_has_label_has_colour AS uplc
ON uplc.user_id = upl.user_id
AND uplc.photo_id = upl.photo_id
AND uplc.label_id = upl.label_id
INNER
JOIN colour
ON colour.id = uplc.colour_id
WHERE upl.user_id = 1
AND upl.photo_id = 2
GROUP
BY label.name
(some unnecessarily verbose names were mercifully renamed)
you are welcome to rewrite this for your auto_increment scenario