danronmoon danronmoon - 6 months ago 17
SQL Question

Select rows by part of composite key that all match the remainder of composite key?

I have a table called "emoji" which stores the code points for each emoji.

CREATE TABLE `emoji` (
`emoji_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`code_point` int(10) unsigned NOT NULL,
`order` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`emoji_id`,`code_point`),
KEY `code_point, order` (`code_point`,`order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO emoji (emoji_id, code_point, `order`) VALUES(1, 127467, 0), (1, 127479, 1), (2, 127472, 0), (2, 127479, 1);


SQLFiddle

Given code points 127467 and 127479, how would I be able to fetch only the rows that share the same
emoji_id
(
1
in this case)?

I've tried the following:

SELECT
emoji_id,
code_point,
`order`
FROM
emoji
WHERE
code_point IN (127467, 127479) AND
emoji_id IN (
SELECT
emoji_id
FROM
emoji
GROUP BY
emoji_id
HAVING
COUNT(emoji_id) > 1
)


but 127479 is a shared code point among a few different emojis, therefore rendering the count filtering useless and also returning the last record in the set in this example.

Answer

You would get the list of emoji's by doing:

SELECT emoji_id
FROM emoji
WHERE code_point IN (127467, 127479) 
GROUP BY emoji_id
HAVING COUNT(emoji_id) = 2;

You can incorporate this into a query to get the details:

select e.*
from emoji
where e.emoji_id in (SELECT emoji_id
                     FROM emoji
                     WHERE code_point IN (127467, 127479) 
                     GROUP BY emoji_id
                     HAVING COUNT(emoji_id) = 2
                    );
Comments