MySQL IN() clause multiple returns

Question

I have a special data environment where I need to be returned data in a certain way to populate a table.

This is my current query:

SELECT 
  bs_id, 
  IF(bs_board = 0, 'All Boards', (SELECT b_name FROM certboards WHERE b_id IN (REPLACE(bs_board, ';', ',')))) AS board 
FROM boardsubs

As you can see I have an if statement then a special subselect.

The reason I have this is that the field bs_board is a varchar field containing multiple row IDs like so:

1;2;6;17

So, the query like it is works fine, but it only returns the first matched b_name. I need it to return all matches. For instance in this was 1;2 it should return two boards Board 1 and Board 2 in the same column. Later I can deal with adding a <br> in between each result.

But the problem I am dealing with is that it has to come back in a single column both name, or all names since the field can contain as many as the original editor selected.

If you can, please normalize the table. You should not have multiple values in a single column. — Kermit
– Kermit, Commented Jan 23, 2013 at 21:47
@njk I cannot change the column type, it will ALWAYS stay as a varchar with values like so 1;2;3, period. That is why I posted here to find if there was a way to parse the way it is now. And yes I should have multiple values, it is perfectly able to work that way, thank you. — jfreak53
– jfreak53, Commented Jan 23, 2013 at 21:58
@jfreak53 "It works" doesn't mean it's a wise idea. Your question amounts to, "How do I normalize my database on the fly every time I need to run this query?" It's perfectly reasonable for us to ask, "Can't you just normalize the schema and be done with it?" — Bacon Bits
– Bacon Bits, Commented Jan 23, 2013 at 22:32
@jfreak53 You should not have multiple values in a single column, just because it is perfectly able to work that way. If you don't feel like using the principles of a relational DBMS, then perhaps you shouldn't be using one. — Kermit
– Kermit, Commented Jan 23, 2013 at 22:44

Bacon Bits · Accepted Answer · 2013-01-23 22:01:14Z

This will not work the way you're thinking it will work.

Let's say bs_board is '1;2;3'

In your query, REPLACE(bs_board, ';', ',') will resolve to '1,2,3', which is a single literal string. This makes your final subquery:

SELECT b_name FROM certboards WHERE b_id IN ('1,2,3')

which is equivalent to:

SELECT b_name FROM certboards WHERE b_id = '1,2,3'

The most correct solution to the problem is to normalize your database. Your current system or storing multiple values in a single field is exactly what you should never do with an RDBMS, and this is exactly why. The database is not designed to handle this kind of field. You should have a separate table with one row for each bs_board, and then JOIN the tables.

There are no good solutions to this problem. It's a fundamental schema design flaw. The easiest way around it is to fix it with application logic. First you run:

SELECT bs_id, bs_board FROM boardsubs

From there you parse the bs_board field in your application logic and build the actual query you want to run:

SELECT bs_id, 
  IF(bs_board = 0, 'All Boards', (SELECT b_name FROM certboards WHERE b_id IN (<InsertedStringHere>) AS board 
FROM boardsubs

There are other ways around the problem, but you will have problems with sorting order, matching, and numerous other problems. The best solution is to add a table and move this multi-valued field to that table.

jfreak53 · Accepted Answer · 2013-01-23 21:43:22Z

1

The b_id IN (REPLACE(bs_board, ';', ',')) will result in b_id IN ('1,2,6,7') which is different from b_id IN (1,2,6,7) which is what you are looking for.

To make it work either parse the string before doing the query, or use prepared statements.

edited Jan 23, 2013 at 21:43

jfreak53

2,3797 gold badges38 silver badges56 bronze badges

answered Jan 23, 2013 at 21:37

slaakso

9,1452 gold badges19 silver badges29 bronze badges

3 Comments

jfreak53 Over a year ago

Could you please elaborate?

slaakso Over a year ago

"b_id IN ('1,2,6,7')" compares b_id to single string value '1,2,6,7' whereas "b_id IN (1,2,6,7)" compares b_id to each 4 value in the set.

jfreak53 Over a year ago

That's my point, it has to stay that way. How do I parse the values out, that is my problem I can't figure out how to convert it from parsing it into '1,2,3' to parse it as 1,2,3

Collectives™ on Stack Overflow

MySQL IN() clause multiple returns

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related