1

I'm trying to select Ids from a table if they were found by a query on another table (my final goal is to delete, from second table, rows with ids in the first query). Here are my attempts:

SELECT @MyList:=GROUP_CONCAT(Id SEPARATOR ', ') AS MyList
FROM myitems
WHERE MyString IS NULL OR MyString = '';

So @MyList contains '41, 42, 49, 51'

Query based on FIND_IN_SET returns only one row

SELECT Id
FROM myitems2
WHERE FIND_IN_SET(Id, @MyList) > 0;

Expected Result:

41
42

Returns

41

I get the same result if I use IN:

SELECT Id
FROM myitems2
WHERE Id IN (@MyList);

I also tryed a query based on LOCATE, but it returns Ids not in the set:

SELECT Id
FROM myitems2
WHERE LOCATE(Id, @MyList) > 0;

Expected Result:

41
42

Returns

1
2
4
5
9
41
42

How can I fix my queries?

genespos
  • 3,211
  • 6
  • 38
  • 70

1 Answers1

2

FIND_IN_SET doesn't works because you have spaces after commas.

Replace :

GROUP_CONCAT(Id SEPARATOR ', ')

By :

GROUP_CONCAT(Id SEPARATOR ',')

Or you can do :

SELECT Id
FROM myitems2
WHERE FIND_IN_SET(Id, replace(@MyList, ' ', ''));
ebahi
  • 536
  • 2
  • 7