Select same value multiple times

Question

I would like to select a value multiple times.

I've tried using the following queries: $id_nums = 1, 1, 1, 1, 2, 2, 2

   SELECT Name FROM Events WHERE ID IN ($id_nums)

The following query did the job although not fully, as IN is removing "duplicates" so the result returned me one row with ID of 1 and 1 row with ID of 2.

My 2nd attempt, was done using this query: $id_nums = (1), (1), (1), (2) , (2)

SELECT Name FROM Events EVNT RIGHT JOIN (SELECT Name FROM (VALUES $id_nums  )AS X(a)) AS test ON EVNT.Name = test.a;

Although that didn't work too, as the syntax is wrong. I can't see the error I made there.

The actual result I am expecting would be that if I select "rows", I would see actually the rows selected. Such as if $id_nums = 1, 1, 1, 1, 2, 2, 2 then I would get 4 rows of data with ID =1, and 3 rows of data with ID = 2

Are your IDs not unique/auto incrementing? I'd cut the PHP out of this and get it working as a mysql query first. — user3783243
– user3783243, Commented Jan 12, 2019 at 17:21
show us the dataset of your Events table. Your Id may be unique — A l w a y s S u n n y
– A l w a y s S u n n y, Commented Jan 12, 2019 at 17:23
My IDs are unique, and they are all incremented. The thing is that I want to fetch data equal to the number of IDs provided, not just "2 rows" if the query is "1, 1, 1" then 3 identical rows need to be fetched >> imgur.com/a/IBGFcSW — Adrian
– Adrian, Commented Jan 12, 2019 at 17:23
You are joining other tables? Please provide sample data and table structure. — user3783243
– user3783243, Commented Jan 12, 2019 at 17:25

Gordon Linoff · Accepted Answer · 2019-01-12 17:26:23Z

2

You need to generate a list of numbers and use left join (or right join, but I much prefer left join. In MySQL, you do this using select and union all:

select e.name
from (select 1 as id union all
      select 1 as id union all
      select 1 as id union all
      select 1 as id union all
      select 2 as id union all
      select 2 as id union all
      select 2 as id
     ) i left join
     events e
     on e.id = i.id;

answered Jan 12, 2019 at 17:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raymond Nijland Over a year ago

topicstarter If you also want to fix the order you can/need to add ORDER BY FIELD(e.id, 1, 1, 1, 1, 2, 2, 2)

A l w a y s S u n n y · Accepted Answer · 2019-01-12 17:28:14Z

1

You can use something like with UNION ALL clause

SELECT Name FROM Events WHERE ID=1
UNION ALL
SELECT Name FROM Events WHERE ID=1
UNION ALL
SELECT Name FROM Events WHERE ID=1
UNION ALL
SELECT Name FROM Events WHERE ID=1
UNION ALL
SELECT Name FROM Events WHERE ID=2
UNION ALL
SELECT Name FROM Events WHERE ID=2

answered Jan 12, 2019 at 17:28

A l w a y s S u n n y

38.8k9 gold badges68 silver badges120 bronze badges

Collectives™ on Stack Overflow

Select same value multiple times

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related