I have some objects which have duplicates. One table stores the objects (the 'primaries'); another stores the one-to-many relation of objects to their duplicates (the 'secondaries'):
Objects
-------
`obj`
A1
A1a
A1b
A2
Duplicates
----------
`primary`,`secondary`
A1, A1a
A1, A1b
I'm trying to devise a single query that uses GROUP_CONCAT to return a summary of objects and their duplicates, returning one row per object: [obj, primary, combined secondary columns], like:
A1, A1,(A1a,A1b)
A1a, A1,(A1a,A1b)
A1b, A1,(A1a,A1b)
A2, NULL
Trying to do
SELECT `obj`, `primary`, GROUP_CONCAT(`secondary`)
FROM `Objects`
LEFT JOIN `Duplicates` on (`obj`=`primary` OR `obj`=`secondary`)
is returning only a single row, containing a concat of all secondaries from all rows:
a1a,a1a, a1b,a1b,a1c,a1c,a2b,a2b
Thanks for any help!
@PM 77-1 (thanks) suggested adding a GROUP BY clause. I tried this:
SELECT `obj`, `primary`, GROUP_CONCAT(`secondary`)
FROM `Objects`
LEFT JOIN `Duplicates` on (`obj`=`primary` OR `obj`=`secondary`) GROUP BY `obj`
which improved things, and gives 4 rows, one for each instance of obj, and the results are what I want for A1 and A2, but each of obj A1a and A1b should be A1a,A1b.
obj primary GROUP_CONCAT(`secondary`)
A1 A1 A1a,A1b
A1a A1 A1a
A1b A1 A1b
A2 NULL NULL
[BTW I forgot that primary is a reserved word so in my test implementation this is actually primaryy!]
So, still stuck on this...
Edit:
I'm not stuck anymore! The result above is what I wanted! Thanks to @PM 77-1.
GROUP BYclause.primaryandsecondarydesignation interchangeably. Can you edit your question and explain the exact logic behind your results forobjvalues ofA1aandA1b?A1,(A1a,A1b)