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?

It is good to have some confidentiality. But I think some [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) should be available. — Asgar, Jul 11 '22 at 12:33
[Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097) — philipxy, Jul 11 '22 at 23:24
Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, Sep 03 '22 at 22:20

score 0 · Answer 1 · answered Jul 11 '22 at 13:59

0

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:

answered Jul 11 '22 at 13:59

Ozan Sen

2,477
2
4
16

Tried that. Note that on line 3 the COUNT(N.id) gives 0 instead of 1. – Guy Arnon Jul 11 '22 at 14:15
I also need (for the real practice) to use SUM() and I cannot use distinct for that. – Guy Arnon Jul 11 '22 at 14:15
@Guy Arnon Id values in Nursery tables {1,2,3}. On line 3 in Main table, only value {4} appears which is not available in id column in nursery table. – Ozan Sen Jul 11 '22 at 14:22
Yes, you are correct - my bad. But still - this solution is good for the COUNT() function, but not to SUM() - if I want to sum prices for instance I cannot use DISTINCT, because 2 products with same price will be considered one. – Guy Arnon Jul 11 '22 at 15:25
I think, if your aim is to sum prices for instances, You will make the grouping by 2 columns then (unique combination (id column, price column). – Ozan Sen Jul 12 '22 at 07:07
[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) – philipxy Sep 03 '22 at 22:24

score 0 · Answer 2 · answered Sep 03 '22 at 22:13

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

In MySQL - 2 LEFT JOINs with FIND_IN_SET show wrong numbers

2 Answers2