2

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    Mediocre 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!

Flight643
  • 51
  • 4
  • I want to echo out an order summary HTML table in a way that if someone wants to buy two 'Cool Products' they are listed as two independent line items (table rows) in the summary table. – Flight643 Oct 04 '11 at 12:53
  • Possible duplicate: http://stackoverflow.com/q/2259160/165154 – Decent Dabbler Oct 04 '11 at 12:57

4 Answers4

2
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.

judda
  • 3,977
  • 1
  • 25
  • 27
1

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

Tom Mac
  • 9,693
  • 3
  • 25
  • 35
Packet Tracer
  • 3,884
  • 4
  • 26
  • 36
0

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.

Ilmari Karonen
  • 49,047
  • 9
  • 93
  • 153
-1
SELECT id,name,count('id') as qty FROM products
where id in (36,34,2) group by id
Punit
  • 1,110
  • 1
  • 8
  • 14
  • I don't think this is going to do what you want it to. You are combining `DISTINCT` with `GROUP BY` and then you also have a non-aggregated value in it (i.e. name) – judda Oct 06 '11 at 15:40