I have a large mysql table (500,000 records). I need to find sets of data that have all the same attributes (KEY, NAME AND VALUE). Some sets have 20 attributes others have 397 per each KEY. The table looks like this. KEY is a foreign key.

1   87   Color  Red
2   87   Size   Big
3   87   Weight 6

4   85   Color  Red
5   85   Size   Big
6   85   Weight 6

7   96   Color  Red
8   96   Size   Small
8   96   Weight 7

I'm trying to write a query where if KEY=87 it finds KEY 85 matches all attributes and values like KEY 87. There are 397 different attributes rather than just the 3 I show here. I can do this in php but it's sloppy and I want to learn more about mysql but I can't get my head around it. Appreciate any help in advance.

  • 1
    Oh dear, the EAV model rears its ugly head again! Is there any way you can change this? – Vérace Sep 26 '17 at 4:02
  • I agree about EAV but if I was to abandon it I would end up with over 397 columns in my table to account for all the attributes and many would be NULL. – user3093141 Sep 27 '17 at 14:57
  • Have a table for each "set" (or entity). Take a look at my answer here. – Vérace Sep 27 '17 at 17:39
  1. In a subquery, fetch all the attributes (I cringe at EAV, too).
  2. GROUP_CONCAT(... ORDER BY ...) as foo to combine them into a single string.
  3. In the outer query, SELECT GROUP_CONCAT(ky) ... GROUP BY foo HAVING COUNT(*) > 1 to get the duplicate kys. (Please don't use the keyword "KEY".)

Give that a try. If you get in trouble, show us SHOW CREATE TABLE and your failed attempt. (Or abandon EAV.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.