Store query result in variables to use in another query in Postgresql

Question

I am new to Postgresql. Please help me to solve it.

Suppose I have three table called 'table_1', 'table_2' & 'table_3'

I want to get data from table_3 that is related to table_1 and table_2

Such as I am retrieving value_1 and value_2 from table_1 and table_2

Query 1:

Select value_1 From table_1

Query 2:

Select value_2 From table_2

Now I want to use these values in table_3

Query 3:

Select * from table_3 where column_1 = value_1 and column_2 = value_2

How to store first 2 values in variables and use it in third query?

You can use information from other tables in a query with join. However, more specifics on what you actually need to accomplish would be necessary to give an answer. — user1919238
– user1919238, Commented Jan 4, 2016 at 7:52
@Devil's Dream it is pointless to ask a general question such as this. There are many ways to "combine" information from diffrent tables. saying value_1,value_2 ... is useless as we don't know if tables are linked with forigen keys. You need to provide an actual 3 table schema. I'm voting to close it as this is way too much genral. — java
– java, Commented Jan 4, 2016 at 8:02
@Devil's Dream you can do this: Select * from table_3 where column_1 = (Select value_1 From table_1) and column_2 = (Select value_2 From table_2) note that this query assumes that there is only 1 row returning from each sub query. I don't post it as an answer as it is very illogical. You should have at least a WHERE that filters your tables in sub queries. If you will post more information about your real tables we will be able to provide a much better answer. — Elad
– Elad, Commented Jan 4, 2016 at 8:14

Abhishek Anand · Accepted Answer · 2021-12-06 16:41:54Z

24

You can use the following query:

with v1 as (select value_1 from table_1),
     v2 as (select value_2 from table_2)
select * from table_3 where column_1 = (select value_1 from v1) 
and column_2 = (select value_2 from v2);

If v1 and v2 are having multiple rows you can use 'in' operator instead of '=' operator.

For more information, please check the PostgreSQL documentation here.

You can also check a post similar to this on Stackoverflow here.

edited Dec 6, 2021 at 16:41

answered Apr 4, 2020 at 9:42

Abhishek Anand

3993 silver badges9 bronze badges

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

3 Comments

DoArNa Over a year ago

This example is incorrect if we refer the documentation link that you posted in here. Last row of the query should be : select * from table_3 where column_1 = (select value_1 from v1) and column_2 = (select value_2 from v2)> In this simple case looks like doesn't make sense because it is simple. If v1 and v2 queries are complicated ones would see the benefits more

Abhishek Anand Over a year ago

That is correct if there are multiple columns in stored values (v1, and v2).

Sean McCarthy Over a year ago

As @DoArNa mentioned, I had to change my query to something like where column_1 = (select value_1 from v1) not where column_1 = v1. That's what the documentation says, and that's what works for me.

Collectives™ on Stack Overflow

Store query result in variables to use in another query in Postgresql

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related