CODEWITHSUNDEEP
sqlpostgresql
dwarner
dwarner

Reputation: 7

Query table based on results of nested query

So I have a table (t1) that has messages and a thread_id. Each thread ID corresponds to a conversation. I run a query for terms in the messages and now I want to requery t1 based on the results of the query on the messages. I cant do a nested one because the final result should be from the original t1. Restating: I want all the conversations that contain certain words. This is in postgresql 9.1.

Upvotes: 0

Views: 67

Answers (1)

Nisan.H
Nisan.H

Reputation: 6352

This would work with Common Table Experssions, which are available from version 8.4 and up:

create table test_table1 (id serial, val1 integer, val2 text);
insert into test_table1 (val1, val2)
select
    v, 'test data ' || v
from generate_series(1,10000);


WITH
    t1 (id, val1, val2)
    as (
        select
            id, val1, val2
        from test_table1
        order by id asc
        limit 1000
    )

    ,t1_condition (id, condition)
    as (
        select
            id, val1 % 5 = 0
        from t1
        where val1 % 3 = 0
    )


select 
    t1.id, t1.val1, t1.val2,
    t1_condition.condition
from t1
left join t1_condition
on t1.id = t1_condition.id

Upvotes: 1

Related Questions