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

Question

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.

So called relational division. See http://stackoverflow.com/questions/31101480/what-is-the-performance-difference-in-mysql-relational-division-in-and-instead for Mysql options for the task. — Serg, May 29 '16 at 20:36

score 1 · Accepted Answer · answered May 29 '16 at 21:02

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
                    );

score 0 · Answer 2 · answered May 29 '16 at 20:31

0

I think you sould use having b ut without subquery

SELECT
distinct 
emoji_id,
code_point,
`order`
FROM  emoji
WHERE  code_point IN (127467, 127479) 
having cont(*)  = 2;

answered May 29 '16 at 20:31

ScaisEdge

131,976
10
91
107

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

2 Answers2