Mysql query return duplicate entries with in and find_int_set

Question

Hello,

I have a mysql query that shows the users last viewed documents. Some sort of history view, and I want to show this in a history tab, but I cannot get this to work.

The history is saved on another server so I'm getting a list of ID's, and I have to get the document's info from the document database. I want it to show duplicate entries in the history (Which is currently not working)

I've tried multiple things to get it to work, and all I got is get the last 25 documents which are selected with a DISTINCT on the id field, but I also want to get duplicates.

This is the query I'm using (Filled with example data)

SELECT 
    stuff_from_table
FROM 
    a_secret_table
WHERE 
    `id`
IN ( 6176, 6176, 6176, 3091, 3454, 5927, 5929, 5938, 1975, 177, 6123, 5943, 4325, 4326, 4327, 6176, 4327, 1990, 3400, 2650, 2649, 2649, 6176, 4297, 6145 ) 
ORDER BY FIND_IN_SET( id, '6176, 6176, 6176, 3091, 3454, 5927, 5929, 5938, 1975, 177, 6123, 5943, 4325, 4326, 4327, 6176, 4327, 1990, 3400, 2650, 2649, 2649, 6176, 4297, 6145') 
LIMIT 0 , 25

This code works, but will not return any duplicates. It will only return like 17 results, because it ignores the duplicates.

Anyone knows how to get this query to also return duplicates? Or do I have to manually add the missing ones in php?

Thanks in advance!

Answer 1

Rather than use an IN clause, you need to join on the id values. IE:

  SELECT t.stuff
    FROM YOUR_TABLE t
    JOIN (SELECT 6176 AS tid,
          UNION ALL
          SELECT 6176
          UNION ALL
          SELECT 6176
          UNION ALL
          SELECT 3091) x ON x.tid = t.id
ORDER BY FIND_IN_SET(t.id, '6176, 6176, 6176, 3091') 
   LIMIT 25

You need to split that comma separated list into a table with a single column - see: http://forge.mysql.com/tools/tool.php?id=4