MySQL table -> Can you return the same row multiple times, in the same query??

Question

SELECT * FROM menu WHERE item_id = 1 OR item_id = 2 OR item_id = 3;

The above statement returns 3 rows. But the statement below only returns 2 rows.

SELECT * FROM menu WHERE item_id = 1 OR item_id = 1 OR item_id = 2;

And I understand why like, but is there a way to force item_id 1 to be returned twice???

Example of what I want to be returned:

id -> 1 Chips €2.50
id -> 1 Chips €2.50
id -> 2 Coke €1.60
--------------------
Total €6.60

Answer 1

You would have to do something like this:

SELECT * FROM menu WHERE item_id = 1
UNION ALL
SELECT * FROM menu WHERE item_id = 1
UNION ALL
SELECT * FROM menu WHERE item_id = 2

Answer 2

You could join on another table, for example

SELECT * FROM menu
INNER JOIN order_items ON menu.item_id = order_items.item_id
WHERE order_id = 123;

Or just duplicate them in your application.

You shouldn't really need to do what you're asking for.

Answer 3

You need a way to generate a dummy rowset to do this, and MySQL lacks it.

You can do the following:

SELECT  *
FROM    (
        SELECT  1 AS x
        UNION ALL
        SELECT  1 AS x
        UNION ALL
        SELECT  2 AS x
        ) dummy
JOIN    menu
ON      menu.id = dummy.x

Answer 4

Use unions:

SELECT * FROM menu WHERE item_id = 1
  UNION ALL
SELECT * FROM menu WHERE item_id = 1
  UNION ALL
SELECT * FROM menu WHERE item_id = 2

Answer 5

why do you want to query db twice for same thing. Just query once and do the modification (add rows or display same row twice) using programming language you are using.

Something like

id -> 1 Chips €2.50 X 2
id -> 2 Coke €1.60
--------------------
Total €6.60

Answer 6

The first answer looks wrong. It should be left outer join with order_items as first table...

SELECT * FROM order_items oi
left outer JOIN menu m
ON oi.item_id = m.item_id
WHERE order_id = 123;