0

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.

5
  • Consider adding a GROUP BY clause. Commented Dec 23, 2022 at 16:11
  • Thank you @PM 77-1, I've tried this and updated my original post. Commented Dec 23, 2022 at 17:12
  • 1
    You seem to be treating your primary and secondary designation interchangeably. Can you edit your question and explain the exact logic behind your results for obj values of A1a and A1b? Commented Dec 23, 2022 at 17:26
  • Seems odd that both a primary and secondary have the same expected results A1,(A1a,A1b) Commented Dec 23, 2022 at 17:54
  • Thnaks to @PM77-1 Commented Dec 23, 2022 at 18:12

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.