How to append a list of ids in the result of a query-CodePudding

i have a query like this:

select name from tbl1 where id in ( select id from tbl2 where code in (12,13,14,15,16))

and then I get the names:

name1
name2
name3 
etc...

How can I modify the query to get the codes used in the second select with the names, like this:

12  name1
13  name2
14  name3
15  name4
16  name5

CodePudding user response：

Right now your query is selecting name from tbl1, so you just need to join those rows against tbl2. It looks like both tables have an id field, so that is what you can join on. You can then simplify your where because the codes are available after the join

select tbl2.code, tbl1.name from tbl1 
join tbl2 on tbl1.id = tbl2.id 
where tbl2.code in (12,13,14,15,16)

CodePudding user response：

As you haven't provided a db schema, I suggest tbl1 and tbl2 are related via id field:

Use simple join on id column.

select tbl1.name, tbl1.id 
from tbl1
join tbl2 on tbl1.id = tbl2.id and code in (12, 13, 14, 15, 16);

Use with-clause query:

with t as (
  select id from tbl2 where code in (12, 13, 14, 15, 16)
)
select tbl1.name, tbl1.id 
from tbl1
join t on t.id = tbl1.id;

See the demo.