-1

let's see I have a simple table like this:

name    id
tom     1
jerry   2
...     ...

And from the outside, I got a list contains the names (tom, jerry, kettie...)

I am trying to use WHERE IN clause to retrieve the id based on the name list.

I can do 

SELECT id FROM mySimpleTable where name in ('tom','jerry','kettie');

So just iterate the name list and generate the contents in the parentheses.

This works, but the results is not in the input order, for example, the input is tom, jerry, kettie, the expected the result is 1,2,3, however, the output actually could be in any order.

Then how can I modify the SQL clause to make sure I get my input and output matched so that I can do the following process accrordingly. I heard JOIN may help in this situation.

chrisTina
  • 2,298
  • 9
  • 40
  • 74
  • what is a `list contains the names` you mean a string or another table? – Juan Carlos Oropeza Nov 13 '15 at 20:21
  • It is a `java arraylist` contains strings or a `JSON` contains strings, it is not another table. – chrisTina Nov 13 '15 at 20:22
  • You want your result be in the order of the table, or the order in the JSON array? – Juan Carlos Oropeza Nov 13 '15 at 20:22
  • whatever, as long as it is 1 to 1 match – chrisTina Nov 13 '15 at 20:23
  • well even when you already got some answer I detect some diferent aproach and some guessing. We will be probably can offer better answer if you provide the expected output and some examples please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) And [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Nov 13 '15 at 20:26
  • so you downvote my question? – chrisTina Nov 13 '15 at 20:28
  • No I didnt. I just give you suggestion of how improve your question so other dont downvote you in the future – Juan Carlos Oropeza Nov 13 '15 at 20:29
  • but if you could upvote my question, so that I can balance the downvotes(I really did not know why some guys downvote this)....thanks!!!!!!! – chrisTina Nov 13 '15 at 20:31
  • Let make a deal. I will upvote if you improve the question. Because right now I still not sure what order you want and I belive Brian has the right answer. btw you shouldnt be here just for the rep ;) – Juan Carlos Oropeza Nov 13 '15 at 20:34
  • ok man.... I guess you did not get my point. I do not care about the order, I care about the 1 to 1 match. so if the input after `IN` is `(tom, jerry, kettie)`, the output should be `1,2,3`, not `2, 1, 3` or ` 2, 3, 1` – chrisTina Nov 13 '15 at 20:37
  • I ask in my second question... then you want the ID match the order in the element on the JSON array? Then use juegen d answer. – Juan Carlos Oropeza Nov 13 '15 at 20:39

6 Answers6

3
SELECT id 
FROM mySimpleTable 
where name in ('tom','jerry','kettie')
order by field(name, 'tom','jerry','kettie')
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

I heard JOIN may help in this situation.

Yes it can help:

SELECT m.id 
FROM mySimpleTable m
JOIN (
  SELECT 'tom' AS name, 1 AS orderNum
  UNION ALL
  SELECT 'jerry' AS name, 2 AS orderNum
  UNION ALL
  SELECT 'kettie' AS name, 3 AS orderNum
) AS sub
ON m.name = sub.name
ORDER BY sub.orderNum ASC;

SqlFiddleDemo

This solution can be also used in different RDBMS. field is MySQL specific.

How it works:

  1. Create derived table/subquery with values you need to check and ordering column
  2. JOIN will return only rows that correspond each other based on name
  3. ORDER BY column you've added in subquery
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

just select id,name from table_a where name in ('tom','jerry','happy') , you will have the combination of the input name and output id.

Kehan Wang
  • 173
  • 1
  • 8
0

this entirely depends on where you're getting the list for your "in" clause.

if it's from somewhere on the outside, you probably should first turn the list into a temp table, adding an id column that indicates the order (see this answer for a start on how to do that) - and then do an inner join with it.

Community
  • 1
  • 1
blueberryfields
  • 45,910
  • 28
  • 89
  • 168
0

I did try to run your SQL query, and me for one did get the resultant output in the same order as that of the input. Well, but still it isn't necessary it would happen the same way every time, so the best way to arrange your output in a particular hierarchy is to use the ORDER BY clause. The syntax would be:

SELECT column_name
FROM table_name
WHERE conditions
ORDER BY column_name;

So in your case, the query would read as:

SELECT id
FROM mysimpletable
WHERE name
IN('tom','jerry','kettie'....)
ORDER BY id;

You can get more help with MySQL concepts here for further information.

Bellatechie30
  • 34
  • 5
-2

Select id from mySimpleTable where name in ('tom','jerry','kettie') Order by id

Brian
  • 548
  • 2
  • 8
  • 22