Using Subqueries with FIND_IN_SET function in MariaDB

Question

I have the following set up:

[clients]: id (ai), name (text), regions (text); [regions]: id (ai - related to region_id in [municipalities]), name (text); [municipalities]: id (ai - related to municipality_id in [postals]), name (text), region_id (int); [postals]: id (ai), name (text), municipality_id (int).

• all the id's are primary keys

The regions column in [clients] contains a string like "1", "1,2" or "1,3,7".

Now, the general idea is the someone enters a specific value for postal name, and the client name is returned when the postal code falls into the specific region. Each municipality belongs to a specific region and each postal code belongs to a specific municipality. A client can work within multiple regions.

I have the following query:

select name from clients where find_in_set((select region_id from municipalities where id = (select municipality_id from postals where name = "string")),(select regions from clients)) != 0

But I keep getting #1242 - Subquery returns more than 1 row, and I don't know why. The searched string should only return 1 municipality_id, which in turn should only return 1 region_id, which should only be found once in the regions string in the [clients] table.

What am I missing here?

Answer 1

Something like this should work, though I have not tested it.

SELECT DISTINCT c.name
FROM clients AS c
JOIN municipalities AS m
  ON FIND_IN_SET(m.region_id, c.regions)
JOIN postals AS p
  ON m.id = p.municipality_id
WHERE p.name = 'string';

This is not the intended use of FIND_IN_SET(). That function is supposed to be used to match values in MySQL's SET data type. It works for matching comma-separated strings too, but it's not a good use case for SQL.

Using FIND_IN_SET() against a comma-separated list is bound to do a table-scan, so it can't be optimized with an index. If you want your query to have better performance, you should not store multivalued attributes as a comma-separated string.

For other bad effects of using comma-separated strings, see my answer to Is storing a delimited list in a database column really that bad?

A multivalued attribute belongs in a child table, with one value per row, and a reference to the municipality. Then you can do your search with joins, and you have an opportunity to create indexes to optimize the query.