CODEWITHSUNDEEP
mysqlsql
Pankaj Khurana
Pankaj Khurana

Reputation: 3271

Need Help Regarding MySQL IN Clause

I have table named collections in which there are fields pk_collectionid and name.

I am generating a string containing the collection ids separated by commas. Format is : 1,1

I want to retrieve the name of all the ids mentioned in the string from collections table.

I have used IN clause under mysql.

My query is:

SELECT name
FROM `collections`
WHERE `pk_collectionid`
IN ( 1, 1 )

Right now it is generating one row as id is common but i want that it should show two rows instead of one even if ids are same.

Upvotes: 0

Views: 51

Answers (2)

Jon
Jon

Reputation: 437376

You should not try to get duplicate rows back from the query -- it's not necessary, it's wasteful, and it's going to be a pain to implement.

Instead of doing this, create a map of id to a whole row from your result set. Then, you can iterate over the collection that includes duplicate ids (the one you used to construct the query in the first place) and use each id to index into the map you created. This way you will be able to process each record individually as many times as needed.

If you mentioned which language you are using, we could also give an example with code.

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272106

This is a very strange requirement. The only solution I could think of is to use a UNION ALL query:

SELECT name FROM `collections` WHERE `pk_collectionid` = 1
UNION ALL
SELECT name FROM `collections` WHERE `pk_collectionid` = 1
-- UNION ALL
-- SELECT name FROM `collections` WHERE `pk_collectionid` = 2
-- UNION ALL
-- SELECT name FROM `collections` WHERE `pk_collectionid` = 3
-- ...

Upvotes: 3

Related Questions