I have this SQL Fiddle: http://sqlfiddle.com/#!9/dd6780/8
I want to filter by specific tag_id and type but this query returns wrong data. It should return only Post title 1 which has t1.tag_id IN (15, 223) AND t1.type = 1 AND t2.tag_id IN (19, 684) AND t2.type = 2 AND t3.tag_id IN (5) AND t3.type = 4.
Post title 2 and Post title 3 doesn't have tag_id 684 AND type 2 and doesn't need to be returned.
Query:
SELECT p.id,
p.title
FROM `posts` p
INNER JOIN tags_table t1 ON p.id=t1.post_id
INNER JOIN tags_table t2 ON p.id=t2.post_id
INNER JOIN tags_table t3 ON p.id=t3.post_id
WHERE
t1.tag_id IN (15, 223) AND t1.type = 1
AND t2.tag_id IN (19, 684) AND t2.type = 2
AND t3.tag_id IN (5) AND t3.type = 4
GROUP BY p.id
ORDER BY p.id DESC
Data:
CREATE TABLE `posts` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `posts` (`id`, `title`) VALUES
(1, 'Post title 1'),
(2, 'Post title 2'),
(3, 'Post title 3'),
(4, 'Post title 4'),
(5, 'Post title 5'),
(6, 'Post title 6'),
(7, 'Post title 7'),
(8, 'Post title 8'),
(9, 'Post title 9'),
(10, 'Post title 10');
CREATE TABLE `tags_table` (
`post_id` mediumint(8) unsigned NOT NULL,
`tag_id` mediumint(8) unsigned NOT NULL,
`type` tinyint(1) NOT NULL,
PRIMARY KEY (`post_id`,`tag_id`,`type`),
KEY `tag_id` (`tag_id`,`post_id`,`type`),
KEY `type` (`type`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tags_table` (`post_id`, `tag_id`, `type`) VALUES
(1, 2, 2),
(1, 2, 6),
(1, 4, 1),
(1, 5, 4),
(1, 5, 5),
(1, 19, 1),
(1, 19, 2),
(1, 36, 2),
(1, 39, 1),
(1, 44, 1),
(1, 57, 4),
(1, 59, 4),
(1, 70, 1),
(1, 71, 1),
(1, 100, 4),
(1, 105, 1),
(1, 115, 1),
(1, 121, 1),
(1, 177, 1),
(1, 193, 1),
(1, 199, 4),
(1, 223, 1),
(1, 225, 1),
(1, 236, 1),
(1, 290, 1),
(1, 326, 1),
(1, 337, 1),
(1, 381, 4),
(1, 426, 1),
(1, 684, 2),
(1, 888, 4),
(1, 926, 2),
(1, 964, 2),
(1, 2111, 4),
(1, 2314, 4),
(1, 2346, 2),
(1, 3303, 4),
(1, 3375, 2),
(1, 3415, 1),
(1, 3692, 4),
(1, 3964, 2),
(1, 4004, 4),
(1, 10151, 4),
(1, 10512, 4),
(1, 14812, 4),
(1, 16204, 4),
(1, 16886, 4),
(1, 17679, 4),
(1, 18266, 4),
(1, 42096, 3 ),
(2, 1, 6),
(2, 2, 4),
(2, 3, 2),
(2, 5, 4),
(2, 15, 1),
(2, 18, 1),
(2, 19, 2),
(2, 35, 1),
(2, 42, 2),
(2, 47, 1),
(2, 50, 1),
(2, 61, 1),
(2, 66, 4),
(2, 93, 2),
(2, 97, 1),
(2, 121, 1),
(2, 148, 1),
(2, 193, 1),
(2, 205, 1),
(2, 223, 1),
(2, 250, 4),
(2, 255, 4),
(2, 268, 4),
(2, 431, 1),
(2, 554, 4),
(2, 632, 4),
(2, 3279, 2),
(2, 3415, 1),
(2, 3530, 2),
(2, 4121, 2),
(2, 11645, 4),
(2, 11646, 4),
(2, 11955, 4),
(2, 13092, 4),
(2, 15427, 4),
(2, 32542, 3),
(3, 1, 6),
(3, 5, 4),
(3, 15, 1),
(3, 19, 2),
(3, 57, 4),
(3, 66, 4),
(3, 77, 4),
(3, 91, 2),
(3, 99, 4),
(3, 196, 4),
(3, 225, 1),
(3, 290, 1),
(3, 303, 1),
(3, 635, 4),
(3, 1243, 4),
(3, 6469, 4),
(3, 16590, 1);
EDIT
I changed my query with this
SELECT p.id,
p.title
FROM `posts` p
INNER JOIN tags_table t1 ON p.id=t1.post_id
INNER JOIN tags_table t2 ON p.id=t2.post_id
INNER JOIN tags_table t3 ON p.id=t3.post_id
WHERE
t1.tag_id IN ( 15, 223 ) AND t1.type = '1'
AND t2.tag_id IN ( 19, 684 ) AND t2.type = '2'
AND t3.tag_id IN ( 5 ) AND t3.type = '4'
GROUP BY p.id
HAVING
COUNT(t1.tag_id) = 2 AND
COUNT(t2.tag_id) = 2 AND
COUNT(t3.tag_id) = 1
ORDER BY g.id DESC
and it doesn't return any post. If i use only COUNT(t1.tag_id) = 2 it returns but seems again it doesn't filter properly because it doesn't take in consideration:
AND t2.tag_id IN ( 19, 684 ) AND t2.type = '2'
AND t3.tag_id IN ( 5 ) AND t3.type = '4'
