0

Given the following sql fiddle http://sqlfiddle.com/#!9/68b56 I have the following tables:

o_pois

id

and

o_routes

id
pois

The pois column from o_routes table is a string that includes the IDs of pois delimited with comma (,) e.g. 100,98,99,101,103,102

I'm trying to run the following query:

select o_pois.* from o_pois
inner join o_routes on o_pois.id IN (o_routes.pois)

where o_routes.id = 21

However, only o_pois.id=100 is returned.

I would like the result to be as if I'd run this query:

select o_pois.* from o_pois
inner join o_routes on o_pois.id IN (100,98,99,101,103,102)

where o_routes.id = 21

Does anyone know how to handle this?

Daniel
  • 667
  • 6
  • 19
  • 2
    Because `IN` needs sets of values, not string. Use `FIND_IN_SET()` – Alma Do Jul 08 '15 at 15:12
  • So, who was the smart guy who decided that it's great to have comma-delimited IDs in a relational database? That made your life miserable, I suggest finding that guy and have your way with him. In case it's you, you have a much better chance at fixing that and then you don't have to resort to hacks, right? Also, the title should be "IN working properly, problem in design". – N.B. Jul 08 '15 at 15:14
  • http://sqlfiddle.com/#!9/68b56/4 – Mihai Jul 08 '15 at 15:16
  • @N.B. I was assigned this project to fix some things. I don't really have time to fix the DB (there are worse things there, believe me) since the project's deadline is ASAP. – Daniel Jul 08 '15 at 15:21
  • @Mihai thanks, your solution works as intended. Post it as an aswer so I could mark it as such. – Daniel Jul 08 '15 at 15:21
  • @Daniel eh just post it yourself i will upvote it but the real answer is to normalize the table.With a few thousands rows this wil be slow and getting only slower – Mihai Jul 08 '15 at 15:22
  • 1
    I believe you have deadlines and that the project is most likely crap, like 99% of software is. However, what seems to be a problem among various companies is that they don't understand the technical debt that comes from badly designed software. Luckily, you got your solution for this particular problem, but just an advice for the future - if you can fix shitty code, there's an easy way to let the management know why it's required - in the long run, they save **a lot** of money. – N.B. Jul 08 '15 at 15:23
  • Obligatory see this question: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – AdamMc331 Jul 08 '15 at 15:24

1 Answers1

1

This:

IN (o_routes.pois)

is executed as

IN ('100,98,99,101,103,102')

which is a single monolithic string/value. It is NOT executed as

IN('100','98','99','101','103','102')

as you think it is.

You should normalize your tables. storing csv values in a single field is usually a sign of bad design.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • While this should be normalized, is there a solution that will work with the given design? – AdamMc331 Jul 08 '15 at 15:23
  • 1
    like @almado said: [find_in_set()](https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set) – Marc B Jul 08 '15 at 15:44