I was wondering, is it possible to join the result of a query with itself, using PostgreSQL?
- 2Could you provide an example of what you're trying to do?Nathan Koop– Nathan Koop2009-05-25 16:48:49 +00:00Commented May 25, 2009 at 16:48
- 1And here I come here, after 12 years of posting this question, and I find that the question and answer are really useful :)hnagaty– hnagaty2022-01-17 18:16:54 +00:00Commented Jan 17, 2022 at 18:16
Add a comment |
3 Answers
You can do so with WITH:
WITH subquery AS( SELECT * FROM TheTable ) SELECT * FROM subquery q1 JOIN subquery q2 on ... Or by creating a VIEW that contains the query, and joining on that:
SELECT * FROM TheView v1 JOIN TheView v2 on ... Or the brute force approach: type the subquery twice:
SELECT * FROM ( SELECT * FROM TheTable ) sub1 LEFT JOIN ( SELECT * FROM TheTable ) sub2 ON ... 
5 Comments
markusk
PostgreSQL does not support WITH queries in the latest version, 8.3, according to postgresql.org/docs/8.3/interactive/….
Alex Martelli
If you can upgrade to 8.4, currently in beta, WITH is finally there, see postgresql.org/docs/8.4/static/queries-with.html
Joril
Since my subquery is somewhat complex, I'll try with a view first, then the "brute force" solution.. Eventually I'll switch to using WITH when pgsql 8.4 ships :) Many thanks to everyone!
ed22
Does the brute-force actually execute the sub-query twice or is it smart enough to reuse the results?
tsadigov
Helped me to solve my problem
Do you mean, the result of a query on a table, to that same table. If so, then Yes, it's possible... e.g.
--Bit of a contrived example but... SELECT * FROM Table INNER JOIN ( SELECT UserID, Max(Login) as LastLogin FROM Table WHERE UserGroup = 'SomeGroup' GROUP BY UserID ) foo ON Table.UserID = Foo.UserID AND Table.Login = Foo.LastLogin 
3 Comments
Eoin Campbell
Sorry, just noticed your "I'm using PostGres" comment. Not sure if the above is syntactically correct in postgres.
Alex Martelli
It's fine AFAICS (except maybe for the 'foo' vs 'Foo' case conflict;-).
Joril
@Eoin: Well I meant joining the results of the queries, but thanks for your help all the same :)
Yes, just alias the queries:
SELECT * FROM ( SELECT * FROM table ) t1 JOIN ( SELECT * FROM table ) t2 ON t1.column < t2.other_column 