Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 9805F9FB97B for ; Mon, 14 May 2007 02:56:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 75789-10 for ; Mon, 14 May 2007 02:56:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id B26D99FB908 for ; Mon, 14 May 2007 02:56:26 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id D8CA130AF3; Mon, 14 May 2007 07:56:25 +0200 (MET DST) From: =?iso-8859-1?q?Rodrigo_De_Le=F3n?= X-Newsgroups: pgsql.sql Subject: Re: table juxtaposition Date: 13 May 2007 22:56:23 -0700 Organization: http://groups.google.com Lines: 71 Message-ID: <1179122183.931677.63420@o5g2000hsb.googlegroups.com> References: <1178077677.003298.217400@n76g2000hsh.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Complaints-To: groups-abuse@google.com In-Reply-To: <1178077677.003298.217400@n76g2000hsh.googlegroups.com> User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; es-AR; rv:1.8.1.3) Gecko/20070309 Firefox/2.0.0.3,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: o5g2000hsb.googlegroups.com; posting-host=201.224.13.252; posting-account=zGrw3Q0AAADU1m7dOiE5S6vTNtvJ7FdA To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/99 X-Sequence-Number: 28379 On May 1, 10:47 pm, workoutlooktest....@gishpuppy.com wrote: > Hello all, > I have a table with 2 issues, issue_A and issue_B, a table with action > items related to the issues, > > issue_A AI_A1 > issue_A AI_A2 > issue_A AI_A3 > issue_B AI_B1 > issue_B AI_B2 > > and network elements that the issues affect: > > issue_A NE_A1 > issue_A NE_A2 > issue_B NE_B1 > issue_B NE_B2 > issue_B NE_B3 > > and I would like a query whose result juxtaposes those 2 tables, as > follows: > > issue_A AI_A1 NE_A1 > issue_A AI_A2 NE_A2 > issue_A AI_A3 NULL > issue_B AI_B1 NE_B1 > issue_B AI_B2 NE_B2 > issue_B NULL NE_B3 > > where the top and bottom NULLs could also be NE_A2, AI_B2 > respectively, I wouldn't mind. > Any idea how to do that? > Thanks, Robin ---------------------------------------- t=# select * from ai; i | ai ---------+------- issue_A | AI_A1 issue_A | AI_A2 issue_A | AI_A3 issue_B | AI_B1 issue_B | AI_B2 t=# select * from ne; i | ne ---------+------- issue_A | NE_A1 issue_A | NE_A2 issue_B | NE_B1 issue_B | NE_B2 issue_B | NE_B3 t=# SELECT COALESCE(ai.i, ne.i) AS i, ai.ai, ne.ne t-# FROM ai FULL JOIN ne ON t-# REPLACE(ai.ai, 'AI', '') = REPLACE(ne.ne, 'NE', ''); i | ai | ne ---------+-------+------- issue_A | AI_A1 | NE_A1 issue_A | AI_A2 | NE_A2 issue_A | AI_A3 | issue_B | AI_B1 | NE_B1 issue_B | AI_B2 | NE_B2 issue_B | | NE_B3 ---------------------------------------- HINT: You might want to normalize your tables further to avoid unnecessary string handling.