Use postgresql query results to form another query

Question

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

Adrian Klaver · Accepted Answer · 2022-07-19 21:16:17Z

2

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

edited Jul 19, 2022 at 21:16

answered Jul 19, 2022 at 18:50

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bergi · Accepted Answer · 2022-07-19 22:15:43Z

1

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');

answered Jul 19, 2022 at 22:15

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

Use postgresql query results to form another query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related