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.
2
-
What version of postgres are you using?Nisan.H– Nisan.H2013-10-25 18:37:04 +00:00Commented Oct 25, 2013 at 18:37
-
1Without seeing your queries it's tough to tell what you're after. Perhaps you should look at Common table expressionsConrad Frix– Conrad Frix2013-10-25 18:40:36 +00:00Commented Oct 25, 2013 at 18:40
Add a comment
|
1 Answer
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
2 Comments
Erwin Brandstetter
A CTE is the right answer (+1), but
t1_condition.condition in the example doesn't make much sense - would always be TRUE by definition.
Nisan.H
@ErwinBrandstetter You're right. I edited to remove the %5 = 0 from the
where clause. I did want the subselect in the example to not just return the full original table, though, because otherwise you could just do the condition test in the original select.