CODEWITHSUNDEEP
phpmysqlsql
Adrian
Adrian

Reputation: 73

Select same value multiple times

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

Upvotes: 1

Views: 1140

Answers (2)

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38502

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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;

Upvotes: 2

Related Questions