Use postgresql query results to form another query-CodePudding

I am trying to select from one table using the select result from another table. I can run this in two queries but would like to optimize it into just one.

First query.. Select ids where matching other id

select id from lookuptable where paid = '547'

This results in something like this

6316352 6316353 6318409 6318410 6320468 6320469 6320470 6322526 6322527 6324586 6324587 6326648

I would like to then use this result to make another selection. I can do it manually like below. Note, there could be many rows with these values so I've been using a IN statement

select * from "othertable" where id in (6316352,6316353,6318409,6318410,6320468,6320469,6320470,6322526,6322527,6324586,6324587,6326648);

CodePudding user response：

select 
  ot.* 
from 
   "othertable" as ot 
join 
   lookuptable as lt 
on 
   ot.id = lt.id 
where 
    lt.paid = '547'

CodePudding user response：

The IN operator supports not just value lists but also subqueries, so you can literally write

select * from "othertable" where id in (select id from lookuptable where paid = '547');