MySQL IN() with duplicate rows?

Is it possible to create a MySQL query like this:

SELECT id, name FROM products WHERE id IN ( 36, 36, 34, 2 )

So that because the id '36' is included twice, it produces two rows of that product in the result?

i.e. the result set might be:

id    name
36    Cool Product
36    Cool Product
34    Lame Product
 2    Mediocre Product

Instead of:

id    name
36    Cool Product
34    Lame Product
 2    Medio开发者_高级运维cre Product

Or possibly to consider it as a 'quantity', like:

id    name                qty
36    Cool Product        2
34    Lame Product        1
 2    Mediocre Product    1

Thanks for the help!

SELECT id, name, COUNT(name) AS qty
FROM products p
WHERE p.id IN ( 36, 34, 2 )
GROUP BY id, name

That said, that is assuming your products table has duplicate records which seems wrong.

you could add 2 results To get the desired result with union all clause, for example:

SELECT id, name FROM products WHERE id IN ( 36 )
UNION ALL
SELECT id, name FROM products WHERE id IN ( 36, 34, 2 )

if you want it as a quantity you could use COUNT and GROUP BY, but if your id index is unique (that it should if is the primary key) you won't get two results of the same product

You could create a temporary table and join against it:

CREATE TEMPORARY TABLE checkout (id INTEGER NOT NULL)

INSERT INTO checkout VALUES (36), (36), (34), (2)

SELECT id, name FROM products NATURAL JOIN checkout

You can also get a quantity column using this method, if you want:

SELECT id, name, COUNT(*) AS qty FROM products NATURAL JOIN checkout GROUP BY id

Of course, in many cases you'd want to store the IDs of the products the user is buying in a permanent table anyway, in which case you don't need the temporary table.

SELECT id,name,count('id') as qty FROM products
where id in (36,34,2) group by id