17

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?

Devil's Dream
  • 655
  • 3
  • 15
  • 38
  • 1
    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. –  Jan 04 '16 at 07:52
  • 1
    I will appreciate if you give an answer :) – Devil's Dream Jan 04 '16 at 07:53
  • It's unclear what do you mean by store values in query. Queries don't store anything, they run and give you result. It will be helpful if you will write what you actually want to do. – Elad Jan 04 '16 at 07:54
  • @Elad I want to use the value_1 of query 1 and value_2 of Query 2 in the third query – Devil's Dream Jan 04 '16 at 08:01
  • 1
    @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 Jan 04 '16 at 08:02
  • 1
    @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 Jan 04 '16 at 08:14
  • 2
    Use a [common table expression](http://www.postgresql.org/docs/current/static/queries-with.html) –  Jan 04 '16 at 08:21

1 Answers1

19

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.

Abhishek Anand
  • 249
  • 3
  • 9
  • 4
    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 – DoArNa Dec 22 '20 at 00:28
  • That is correct if there are multiple columns in stored values (v1, and v2). – Abhishek Anand Jan 20 '21 at 13:51
  • 2
    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. – Sean McCarthy May 13 '21 at 18:18