select one row multiple time when using IN()

Question

I have this query :

select 
     name 
from 
     provinces 
WHERE 
     province_id IN(1,3,2,1)  
ORDER BY FIELD(province_id, 1,3,2,1)

the Number of values in IN() are dynamic

How can I get all rows even duplicates ( in this example -> 1 ) with given ORDER BY ?

the result should be like this :

name1
name3
name2
name1

plus I shouldn't use UNION ALL :

select * from provinces WHERE province_id=1
UNION ALL
select * from provinces WHERE province_id=3
UNION ALL
select * from provinces WHERE province_id=2
UNION ALL
select * from provinces WHERE province_id=1

What is wrong with using `union all`? Any other clauses we should know of that you don't want to use? — trincot, May 27 '16 at 17:14
@MarshallTigerus it's actually a path so I need to get names respectively — Ali, May 27 '16 at 17:16
@trincot i'm ok with UNION ALL but my boss has problem with this method ! — Ali, May 27 '16 at 17:19
In MySQL you can use [this](http://stackoverflow.com/a/11835246/5690722) to split a string parameter into a helper table. — Y.B., May 27 '16 at 17:19
Did any of the answers meet your needs? If so, could you mark the answer of your choice as accepted or else leave a comment? — trincot, Jun 06 '16 at 07:08
In that case could you leave a comment below the answers explaining what the issue is with them? At least I would be grateful if you could do that for my answer, so I could take that into account and possibly improve the answer. Thanks in advance. — trincot, Jun 12 '16 at 15:48

score 4 · Accepted Answer · edited May 23 '17 at 12:24

4

You need a helper table here. On SQL Server that can be something like:

SELECT name
FROM (Values (1),(3),(2),(1)) As list (id) --< List of values to join to as a table
INNER JOIN provinces ON province_id = list.id

Update: In MySQL Split Comma Separated String Into Temp Table can be used to split string parameter into a helper table.

edited May 23 '17 at 12:24

Community

1
1

answered May 27 '16 at 17:15

Y.B.

3,526
14
24

score 1 · Answer 2 · answered May 27 '16 at 19:39

To get the same row more than once you need to join in another table. I suggest to create, only once(!), a helper table. This table will just contain a series of natural numbers (1, 2, 3, 4, ... etc). Such a table can be useful for many other purposes.

Here is the script to create it:

create table seq (num int);
insert into seq values (1),(2),(3),(4),(5),(6),(7),(8);
insert into seq select num+8  from seq;
insert into seq select num+16 from seq;
insert into seq select num+32 from seq;
insert into seq select num+64 from seq;
/* continue doubling the number of records until you feel you have enough */

For the task at hand it is not necessary to add many records, as you only need to make sure you never have more repetitions in your in condition than in the above seq table. I guess 128 will be good enough, but feel free to double the number of records a few times more.

Once you have the above, you can write queries like this:

select     province_id,
           name,
           @pos := instr(@in2 := insert(@in2, @pos+1, 1, '#'), 
                         concat(',',province_id,',')) ord
from       (select @in := '0,1,2,3,1,0', @in2 := @in, @pos := 10000) init
inner join provinces 
        on find_in_set(province_id, @in)
inner join seq
        on num <= length(replace(@in, concat(',',province_id,','), 
                                      concat(',+',province_id,',')))-length(@in)
order by   ord asc

Output for the sample data and sample in list:

| province_id |   name | ord |
|-------------|--------|-----|
|           1 | name 1 |   2 |
|           2 | name 2 |   4 |
|           3 | name 3 |   6 |
|           1 | name 1 |   8 |

SQL Fiddle

How it works

You need to put the list of values in the assignment to the variable @in. For it to work, every valid id must be wrapped between commas, so that is why there is a dummy zero at the start and the end.

By joining in the seq table the result set can grow. The number of records joined in from seq for a particular provinces record is equal to the number of occurrences of the corresponding province_id in the list @in.

There is no out-of-the-box function to count the number of such occurrences, so the expression at the right of num <= may look a bit complex. But it just adds a character for every match in @in and checks how much the length grows by that action. That growth is the number of occurrences.

In the select clause the position of the province_id in the @in list is returned and used to order the result set, so it corresponds to the order in the @in list. In fact, the position is taken with reference to @in2, which is a copy of @in, but is allowed to change:

While this @pos is being calculated, the number at the previous found @pos in @in2 is destroyed with a # character, so the same province_id cannot be found again at the same position.

score 0 · Answer 3 · answered May 27 '16 at 17:10

Its unclear exactly what you are wanting, but here's why its not working the way you want. The IN keyword is shorthand for creating a statement like ....Where province_id = 1 OR province_id = 2 OR province_id = 3 OR province_id = 1. Since province_id = 1 is evaluated as true at the beginning of that statement, it doesn't matter that it is included again later, it is already true. This has no bearing on whether the result returns a duplicate.

select one row multiple time when using IN()

3 Answers3

How it works