Ray,
Have come back to this today and the issue is that I am grouping by RootModelNumber on the original query not by id so the outer join is pulling all the results. At most the query returns a couple of hundred results and I am paginating so at most 20 per page so it shouldn't be to bad.
UPDATE: I just discovered I can use the keyword DISTINCT within GROUP_CONCAT I had no idea I could do that, this makes it altogether possible now.
2nd UPDATE: although that solves my 1st issue it now removes the other colours when filtering which isn't what I want so back to the HAVING clause again
Thanks