In MySQL - 2 LEFT JOINs with FIND_IN_SET show wrong numbers

Question

I have one main table and 2 tables that I left-join using "FIND_IN_SET". When I use a function such as COUNT() or SUM() I get wrong numbers!

They are totally fine when I left-join only one table.

I simplified the tables for the example:

CREATE TABLE `Beatles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `Beatles` VALUES (1,'Paul',80),(2,'Ringo',82),(3,'John',81),(4,'George',79);

CREATE TABLE `Nursery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `Nursery` VALUES (1,'David',2),(2,'Alan',3),(3,'Dan',1);

CREATE TABLE `main` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `nurseryIds` text CHARACTER SET utf8,
  `beatlesIds` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
)

INSERT INTO `main` VALUES (1,'party','1,2','3'),(2,'joy','3','2,4'),(3,'good','4','1'),(4,'all together now','1,2,3','1,2,3,4');

I ran the query:

SELECT M.id, nurseryIds, COUNT(N.id), beatlesIds, COUNT(B.id) FROM guy_test.main M
LEFT JOIN Beatles AS B ON FIND_IN_SET(B.id, M.beatlesIds)
LEFT JOIN Nursery AS N ON FIND_IN_SET(N.id, M.nurseryIds)
GROUP BY M.id

The expected result is: (The "count()" counts the number of ids to its left)

id	nurseryIds	COUNT(N.id)	beatlesIds	COUNT(B.id)
1	1,2	2	3	1
2	3	1	2,4	2
3	4	1	1	1
4	1,2,3	3	1,2,3,4	4

The actual result:

id	nurseryIds	COUNT(N.id)	beatlesIds	COUNT(B.id)
1	1,2	2	3	2
2	3	2	2,4	2
3	4	0	1	1
4	1,2,3	12	1,2,3,4	12

What am I doing wrong? Is it something with the grouping?

Answer 1

You need to put 'DISTINCT' keyword before COUNT functions in the field list(or select list). Please try this:

SELECT M.id, nurseryIds, COUNT(DISTINCT N.id), beatlesIds, COUNT(DISTINCT B.id) FROM main M
LEFT JOIN Beatles AS B ON FIND_IN_SET(B.id, M.beatlesIds)
LEFT JOIN Nursery AS N ON FIND_IN_SET(N.id, M.nurseryIds)
GROUP BY M.id;

If we execute the query:

Answer 2

I just sorted my query on this, mine uses JOIN FIND_IN_SET and has SUM, try this:

SELECT M.id, nurseryIds, COUNT(N.id), beatlesIds, COUNT(B.id) FROM guy_test.main M
LEFT JOIN Beatles AS B ON FIND_IN_SET(B.id, M.beatlesIds) = 1
LEFT JOIN Nursery AS N ON FIND_IN_SET(N.id, M.nurseryIds) = 1 
GROUP BY M.id