CODEWITHSUNDEEP
javascriptsqlite
Tarek Eldeeb
Tarek Eldeeb

Reputation: 606

Returning duplicate entries

I have a table with _id,name. I use SQLite3 queries to fetch names given id, like the following api.

getName(id) >> Query "select name from table where _id=id"

and this other handy API for an array of IDs, the returned rows (names) must match the order of requested IDs.

getNames(ids) >> 
    /* Returned text must be ordered */
    var ordClause='ORDER BY CASE _id';
    for(var i=1;i<=ids.length;i++){ ordClause += ' WHEN '+ids[i-1]+' THEN '+i; }
    ordClause += ' END';
    Query "select name from table where _id in( "+ids+" ) "+ordClause

The problem appears when the given ids have duplicate values. Redundant ids are mapped to the first id only. Thus the returned rows may be less than the requested IDs.

How can I possibly solve this problem?

Upvotes: 0

Views: 28

Answers (1)

CL.
CL.

Reputation: 180080

A SELECT query returns any row that matches the filter in the WHERE clause. Redundant IDs in the IN set do not change the fact that a single row matches that filter.

If you actually want duplicate rows, you have to write a separate query for each of them:

SELECT name FROM MyTable WHERE ID = 7
UNION ALL
SELECT name FROM MyTable WHERE ID = 8
UNION ALL
SELECT name FROM MyTable WHERE ID = 8
UNION ALL
SELECT name FROM MyTable WHERE ID = 7
UNION ALL
SELECT name FROM MyTable WHERE ID = 1;

Upvotes: 1

Related Questions