Geek Answers Handbook

Get all merged records when one match is found

Here is a simple test database that should explain my problem:

CREATE TABLE `articles` (
  `article_id` int(11) NOT NULL AUTO_INCREMENT,
  `article_title` varchar(50) NOT NULL,
  PRIMARY KEY (`article_id`)
);

INSERT INTO `articles` VALUES(1, 'first article');
INSERT INTO `articles` VALUES(2, 'second article');

CREATE TABLE `flags` (
  `flag_id` int(11) NOT NULL AUTO_INCREMENT,
  `flag_name` varchar(50) NOT NULL,
  PRIMARY KEY (`flag_id`)
);

INSERT INTO `flags` VALUES(1, 'red');
INSERT INTO `flags` VALUES(2, 'blue');
INSERT INTO `flags` VALUES(3, 'green');
INSERT INTO `flags` VALUES(4, 'orange');
INSERT INTO `flags` VALUES(5, 'purple');

CREATE TABLE `map` (
  `map_article_id` int(11) NOT NULL,
  `map_flag_id` int(11) NOT NULL,
  PRIMARY KEY (`map_article_id`,`map_flag_id`),
  KEY `map_flag_id` (`map_flag_id`)
);

INSERT INTO `map` VALUES(1, 1);
INSERT INTO `map` VALUES(1, 2);
INSERT INTO `map` VALUES(2, 2);
INSERT INTO `map` VALUES(2, 3);
INSERT INTO `map` VALUES(1, 4);

raw materials:

article_id  article_title
1   first article
2   second article

flag_id     flag_name
1   red
2   blue
3   green
4   orange
5   purple

map_article_id  map_flag_id
1   1
1   2
2   2
2   3
1   4

One table with articles, one with flags and one with a description flag.

Selecting all articles with combined flags is simple and works correctly:

SELECT `article_id` , `article_title` , GROUP_CONCAT( `flag_name` )
FROM `articles`
LEFT JOIN `map` ON `map_article_id` = `article_id`
LEFT JOIN `flags` ON `flag_id` = `map_flag_id`
GROUP BY `article_id`

Results:

article_id  article_title   GROUP_CONCAT(`flag_name`)   
1   first article   red,blue,orange
2   second article  blue,green

The problem is that I want to find all articles with a specific flag specified, but with the GROUP_CONCAT field intact. When I add WHERE map_flag_id= 1, the query returns:

article_id  article_title   GROUP_CONCAT(`flag_name`)   
1   first article   red

How to get only an article with red, but with all the red, blue, orange flags in the last column? Please do not offer "LIKE% red%", I need it to be fast.

thank

EDIT

May be,?

SELECT `article_id` , `article_title` , (

SELECT GROUP_CONCAT( `flag_name` )
FROM `flags`
LEFT JOIN `map` ON `map_flag_id` = `flag_id`
WHERE `map_article_id` = `article_id`
)

FROM `articles`
LEFT JOIN `map` ON `map_article_id` = `article_id`
LEFT JOIN `flags` ON `flag_id` = `map_flag_id`
WHERE `flag_id` =1
GROUP BY `article_id`
+4
1

:

1. SELECT article_id , article_title , GROUP_CONCAT( flag_name ) AS flags FROM LEFT JOIN ON map_article_id = article_id LEFT JOIN ON flag_id = map_flag_id GROUP BY article_id HAVING FIND_IN_SET('red', flags)

  • `SELECT a.article_id, a.article_title, GROUP_CONCAT (f.flag_name) AS FROM map m JOIN articles a ON m.map_article_id = a.article_id m.map_flag_id = 1 LEFT JOIN map m1 ON m1.map_article_id = a.article_id LEFT JOIN f ON f.flag_id = m1.map_flag_id

    GROUP BY a.article_id `

+2

More articles:


All Articles