CODEWITHSUNDEEP
phpmysql
Lal krishnan S L
Lal krishnan S L

Reputation: 1745

SELECT same row as repeated result from mysql DB

I have a table like

+------+----------+
| id   | location |
+------+----------+
|    1 | TVM      |
|    2 | KLM      |
|    3 | EKM      |
+------+----------+

And I have an array of id like [1,2,1,3,1]. I need to get the result as

+------+----------+
| id   | location |
+------+----------+
|    1 | TVM      |
|    2 | KLM      |
|    1 | TVM      |
|    3 | EKM      |
|    1 | TVM      |
+------+----------+

I am already tried WHERE IN like conditions but no luck.

Upvotes: 1

Views: 109

Answers (2)

spencer7593
spencer7593

Reputation: 108370

You could also return this result with a query like this; this uses a separate SELECT to return each occurrence of row with id=1.

  ( SELECT id, location FROM mytable WHERE id     IN (1,2)
    ORDER BY id
  )
  UNION ALL
  ( SELECT id, location FROM mytable WHERE id     IN (1,3)
    ORDER BY id
  )
  UNION ALL
  ( SELECT id, location FROM mytable WHERE id     IN (1)
    ORDER BY id
  )

Following a similar pattern, the result could be obtained by combining the results from five SELECT statements, each returning a separate row. That would probably be a little simpler to achieve from a small array, e.g.

$glue = ") ) UNION ALL
  (SELECT id, location FROM mytable WHERE id IN (";

$sql = "(SELECT id, location FROM mytable WHERE id IN (" 
     . implode($glue, $myarray)
     . ") )";

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

A where statement cannot multiply the number of rows. It can only filter rows out. You want a join:

select tl.*
from tablelike tl join
     (select 1 as n union all select 2 union all select 1 union all
      select 3 union all select 1
     ) n
     on tl.id = n.n;

Note: if you are already generating the list via a query or from a table, then use that for the query rather than passing the list out of the database and then back in.

Upvotes: 3

Related Questions