CODEWITHSUNDEEP
mysql
Daniel
Daniel

Reputation: 667

MySQL - inner join using IN not working properly

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?

Upvotes: 0

Views: 40

Answers (1)

Marc B
Marc B

Reputation: 360662

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.

Upvotes: 1

Related Questions