[SOLVED] How to also list duplicates values within IN?

Issue

I have a query where the elements I want to show come from a big list of elements where you can have the same element several times and I need to show all the elements from that list, even those elements that are repeated.

Table is called features and its fields are id, name, type.

In this example the list of elements is (1, 15, 15, 15, 23, 46) where 15 is repeated three times.

My initial query (it does not work):

SELECT f.* FROM features f WHERE f.id IN (1, 15, 15, 15, 23, 46)

When I run the query it only shows me elements 1,15,23, 46 and I’d love to see a query listing you all those 6 elements. Is that possible? How?

EDIT: This data (features) is used to create cards for a deck of cards. The cards are chosen by the user and they can choose the same card several times.

Solution

The list must be a rowset in FROM, not a list in WHERE:

SELECT f.* 
FROM features f 
JOIN (SELECT 1 AS id UNION ALL
      SELECT 15 UNION ALL
      SELECT 15 UNION ALL
      SELECT 15 UNION ALL
      SELECT 23 UNION ALL
      SELECT 46) criteria USING (id) 

Answered By – Akina

Answer Checked By – Clifford M. (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.