I have been trying for hours to get all the IDs where 2 different meta_key's meta_value are the same.

E.g. I would expect the below example to return user_id 1 and 2 as their first_name and last_name's meta_value are exactly the same under the same user_id.

Thanks in advance!

CodePudding user response：

In MySQL, you could just use aggregation:

select vals, group_concat(user_id) as user_ids
from (select user_id,
             group_concat(meta_value order by meta_key) as vals
      from t
      where meta_key in ('first_name', 'last_name')
      group by user_id
      having count(*) = 2  -- both keys present
     )
group by vals
having count(*) >= 2;

This returns the concatenated keys with a list of user ids that have those values.

Note: This particular formulation assumes that the names have no commas in them (the default separator for group_concat()). You can adjust the separate to a less commonly used character, such as |, if that is an issue.

CodePudding user response：

You can self join the table

CREATE TABLE tab1
    (`user_id` int, `meta_key` varchar(10), `meta_value` varchar(5))
;
    
INSERT INTO tab1
    (`user_id`, `meta_key`, `meta_value`)
VALUES
    (0, 'first_name', 'Joe'),
    (0, 'last_name', 'Doe'),
    (1, 'first_name', 'Peter'),
    (1, 'last_name', 'Peter'),
    (2, 'first_name', 'Simon'),
    (2, 'last_name', 'Simon')
;

SELECT DISTINCT a.user_id FROM tab1 a INNER JOIN tab1 b 
ON a.`meta_value` = b.`meta_value` AND a.`meta_key` <> b.`meta_key` 
AND a.`meta_key` = 'first_name' AND b.`meta_key` = 'last_name'

| user_id |
| ------: |
|       1 |
|       2 |

db<>fiddle here