Question 89889 in PostgreSQL Question posted in
The official documentation can be found here.

How to append a list of ids in the result of a query – Postgresql

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
Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

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

    1. 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);
    1. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search