CODEWITHSUNDEEP
mysql
The Disintegrator
The Disintegrator

Reputation: 4187

Help with mysql IN clause

I have a table with a numeric ID and a barcode and need to convert a given list of IDs to the corresponding list of barcodes. But if I need one barcode for every ID, even if there is more than one of a given ID. The first thing I tried was

SELECT barcode FROM prod WHERE prodID IN (76,76,76,88,88);

But I only got 2 barcodes (one for the ID 76 and one for the ID 88), not 5 as I needed.

Any idea of how to do that without having to run one query per ID?

Upvotes: 0

Views: 75

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

I assume you need 5 rows to be returned for your given example, regardless if they are the same prodID or not.

One solution would be to JOIN your table with a on the fly created table returning all the prodID's you need.

SELECT  p.Barcode 
FROM    prod p
        INNER JOIN (
          SELECT 76 AS prodID
          UNION ALL SELECT 76 
          UNION ALL SELECT 76 
          UNION ALL SELECT 88
          UNION ALL SELECT 88
        ) i ON i.prodID = p.prodID

Upvotes: 1

esskar
esskar

Reputation: 10970

i would handle that in your code that receives the result. it makes no sense to get 5 results here, since you already have all the information you asked for: the barcodes for prodID 76 and 88

Upvotes: 1

Related Questions