Обсуждение: JOIN a UNION
Simple question: Is there a way to do something like the following: SELECT x,y,z FROM A UNION SELECT x,y,z FROM B JOIN C ON C.z = B.z Thanks, Dave
I just realized what I posted was perfectly valid SQL. The following is more what I had in mind: (SELECT x,y,z FROM A UNION SELECT x,y,z FROM B) AB JOIN C ON C.z = AB.z This version certainly does throw an error. Basically I'm wondering if there is a way to get Postgres to treat the result of a UNION as a table on which I could perform subsequent operations (like a JOIN). On Aug 6, 10:43 am, "david.schruth" <dschr...@gmail.com> wrote: > Simple question: > > Is there a way to do something like the following: > > SELECT x,y,z FROM A > UNION > SELECT x,y,z FROM B > JOIN C ON C.z = B.z > > Thanks, > > Dave
Hello SELECT ... FROM (SELECT ... FROM A UNION ALL SELECT FROM B) s1 JOIN C IN C.z = s1.z; Regards Pavel Stehule 2009/8/6 david.schruth <dschruth@gmail.com>: > I just realized what I posted was perfectly valid SQL. The following > is more what I had in mind: > > (SELECT x,y,z FROM A > UNION > SELECT x,y,z FROM B) > AB JOIN C ON C.z = AB.z > > This version certainly does throw an error. > > Basically I'm wondering if there is a way to get Postgres to treat the > result of a UNION as a table on which I could perform subsequent > operations (like a JOIN). > > On Aug 6, 10:43 am, "david.schruth" <dschr...@gmail.com> wrote: >> Simple question: >> >> Is there a way to do something like the following: >> >> SELECT x,y,z FROM A >> UNION >> SELECT x,y,z FROM B >> JOIN C ON C.z = B.z >> >> Thanks, >> >> Dave > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Thu, Aug 06, 2009 at 08:33:57PM +0200, Pavel Stehule wrote: > Hello > > SELECT ... FROM > (SELECT ... FROM A > UNION ALL > SELECT FROM B) s1 > JOIN C IN C.z = s1.z; That last line should read: JOIN C ON C.z = s1.z; Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
2009/8/6 David Fetter <david@fetter.org>: > On Thu, Aug 06, 2009 at 08:33:57PM +0200, Pavel Stehule wrote: >> Hello >> >> SELECT ... FROM >> (SELECT ... FROM A >> UNION ALL >> SELECT FROM B) s1 >> JOIN C IN C.z = s1.z; > > That last line should read: > > JOIN C ON C.z = s1.z; I am sorry Pavel > > Cheers, > David. > -- > David Fetter <david@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate >
Thanks that did it! On Aug 6, 11:33 am, pavel.steh...@gmail.com (Pavel Stehule) wrote: > Hello > > SELECT ... FROM > (SELECT ... FROM A > UNION ALL > SELECT FROM B) s1 > JOIN C IN C.z = s1.z; > > Regards > Pavel Stehule > > 2009/8/6 david.schruth <dschr...@gmail.com>: > > > > > I just realized what I posted was perfectly valid SQL. The following > > is more what I had in mind: > > > (SELECT x,y,z FROM A > > UNION > > SELECT x,y,z FROM B) > > AB JOIN C ON C.z = AB.z > > > This version certainly does throw an error. > > > Basically I'm wondering if there is a way to get Postgres to treat the > > result of a UNION as a table on which I could perform subsequent > > operations (like a JOIN). > > > On Aug 6, 10:43 am, "david.schruth" <dschr...@gmail.com> wrote: > >> Simple question: > > >> Is there a way to do something like the following: > > >> SELECT x,y,z FROM A > >> UNION > >> SELECT x,y,z FROM B > >> JOIN C ON C.z = B.z > > >> Thanks, > > >> Dave > > > -- > > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > > To make changes to your subscription: > >http://www.postgresql.org/mailpref/pgsql-general > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general