From crashbandicootu@gmail.com Wed Jul 30 22:08:27 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCc2t-0003OI-4h for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 22:08:27 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XCc2s-0005Cf-KR for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 22:08:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XCc2r-0005CZ-OJ for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 22:08:25 +0000 Received: from mail-ig0-x243.google.com ([2607:f8b0:4001:c05::243]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XCc2o-0005My-4G for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 22:08:24 +0000 Received: by mail-ig0-f195.google.com with SMTP id uq10so823040igb.2 for ; Wed, 30 Jul 2014 15:08:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:date:message-id:subject:from:to:content-type; bh=xfUsqV085CosuDL+GdnH5grrBMJTD/4uGhycwnJCoh4=; b=iWU71ti/uJeDBrb+zuYRFq6Xp2Qn8fQyHEPjm7AFBzWCacz62xOjA4blocjXqsyXAn cFz7CWhM5me/9kJQXa0M58an2r8T4ROrQdpXpSD6FgcBVbmr7NUIKQetYfc3AxM+ZifX gKpLwJJYBAXkknlAnGc7iXX7VcGAaIBcZwV2zdiolESFrpQt0TDHHEvY98YGHFSxb8sj Wt0JfQL/L1SsRZdlgDg9yyPSwkdcMVUsC5HzeLYARMg9xOK3xHYeOYPr2Tg+PdWR1pLC Xug1ZTJXNU7T5CLey5JFRgIO1U0wcNNw/awCkjM/QtyebtmsCKJTFn9INq3tBo4gA2jR ptGw== MIME-Version: 1.0 X-Received: by 10.50.67.51 with SMTP id k19mr12119737igt.39.1406758098745; Wed, 30 Jul 2014 15:08:18 -0700 (PDT) Received: by 10.107.160.11 with HTTP; Wed, 30 Jul 2014 15:08:18 -0700 (PDT) Date: Wed, 30 Jul 2014 15:08:18 -0700 Message-ID: Subject: Reg: Sql Join From: CrashBandi To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=047d7bd756e6c2ceb004ff706420 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --047d7bd756e6c2ceb004ff706420 Content-Type: text/plain; charset=UTF-8 Hi, I am having the following question. I am not sure how to approach it. Please help! table A name col1 col2 col3 col4 apple 100 11111 1 APL orange 200 22222 3 ORG carrot 300 33333 3 CRT table B custom_name value obj_type obj_id apple a FR 100 orange o FR 200 carrot c VG 300 apple d FR 11111 orange e VG 22222 carrot f UC 33333 apple h VG 1 orange o FR 3 carrot c VG 3 when obj_type ='FR' then join on col1 When obj_type='VG' then join on col2 When obj_type='UC' then join on col2 Thanks In advance, CB --047d7bd756e6c2ceb004ff706420 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

I am having the followin= g question. I am not sure how to approach it. Please help!

table A
name c= ol1 c= ol2 c= ol3 c= ol4
= apple 100 11111 1 APL=
= orange 200 22222 3 ORG=
= carrot 300 33333 3 CRT=


table B
custom_n= ame value obj_type obj_id
apple a FR 100
orange o FR 200
carrot c VG 300
apple d FR 11111
orange e VG 22222
carrot f UC 33333
apple h VG 1
orange o FR 3
carrot c VG 3

when obj_type =3D= 'FR' then join on col1
When obj_type=3D'VG' then = join on col2
When obj_type=3D'UC' then join on col2
=

Thanks In advance,
CB
=C2=A0
--047d7bd756e6c2ceb004ff706420-- From david.g.johnston@gmail.com Wed Jul 30 22:45:32 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCccm-0005UH-HU for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 22:45:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XCccl-0000nf-4o for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 22:45:31 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XCccj-0000n9-0n for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 22:45:29 +0000 Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XCccf-0006WH-Hq for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 22:45:26 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1XCcce-0001Ey-95 for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 15:45:24 -0700 Date: Wed, 30 Jul 2014 15:45:24 -0700 (PDT) From: David G Johnston To: pgsql-sql@postgresql.org Message-ID: <1406760324275-5813363.post@n5.nabble.com> In-Reply-To: References: Subject: Re: Reg: Sql Join MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 2.7 (++) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org CrashBandi wrote > Hi, > > I am having the following question. I am not sure how to approach it. > Please help! > > table A > name col1 col2 col3 col4 apple 100 11111 1 APL orange 200 22222 3 ORG > carrot 300 33333 3 CRT > > table B > custom_name value obj_type obj_id apple a FR 100 orange o FR 200 > carrot > c VG 300 apple d FR 11111 orange e VG 22222 carrot f UC 33333 apple h > VG > 1 orange o FR 3 carrot c VG 3 > when obj_type ='FR' then join on col1 > When obj_type='VG' then join on col2 > When obj_type='UC' then join on col2 > > Thanks In advance, > CB You cannot do conditional joins in this manner. You will need to write three joins, one each against a subquery with the appropriate where clause. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Reg-Sql-Join-tp5813360p5813363.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From david.g.johnston@gmail.com Wed Jul 30 22:49:13 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCcgL-0005bz-Di for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 22:49:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XCcgK-0001wb-U6 for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 22:49:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XCcgJ-0001wU-Vb for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 22:49:12 +0000 Received: from sam.nabble.com ([216.139.236.26]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XCcgG-000666-4r for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 22:49:10 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1XCcgD-0001Mc-Ix for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 15:49:05 -0700 Date: Wed, 30 Jul 2014 15:49:05 -0700 (PDT) From: David G Johnston To: pgsql-sql@postgresql.org Message-ID: <1406760545580-5813364.post@n5.nabble.com> In-Reply-To: <1406760324275-5813363.post@n5.nabble.com> References: <1406760324275-5813363.post@n5.nabble.com> Subject: Re: Reg: Sql Join MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 0.8 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org David G Johnston wrote > > CrashBandi wrote >> Hi, >> >> I am having the following question. I am not sure how to approach it. >> Please help! >> >> table A >> name col1 col2 col3 col4 apple 100 11111 1 APL orange 200 22222 3 ORG >> carrot 300 33333 3 CRT >> >> table B >> custom_name value obj_type obj_id apple a FR 100 orange o FR 200 >> carrot >> c VG 300 apple d FR 11111 orange e VG 22222 carrot f UC 33333 apple h >> VG >> 1 orange o FR 3 carrot c VG 3 >> when obj_type ='FR' then join on col1 >> When obj_type='VG' then join on col2 >> When obj_type='UC' then join on col2 >> >> Thanks In advance, >> CB > You cannot do conditional joins in this manner. You will need to write > three joins, one each against a subquery with the appropriate where > clause. > > David J. Actually you might be able to do: On (case obj_type when 'xxx' then col1 when 'yyy' then col2 end = obj_id) But I haven't tried something like this before. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Reg-Sql-Join-tp5813360p5813364.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From oliveiros.cristina@gmail.com Wed Jul 30 22:50:59 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCci2-0005kK-UV for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 22:50:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XCci2-0003mm-AN for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 22:50:58 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XCci1-0003mg-C2 for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 22:50:57 +0000 Received: from mail-wg0-x234.google.com ([2a00:1450:400c:c00::234]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XCchy-0006g1-1e for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 22:50:55 +0000 Received: by mail-wg0-f52.google.com with SMTP id a1so1890603wgh.11 for ; Wed, 30 Jul 2014 15:50:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=references:in-reply-to:mime-version:content-transfer-encoding :content-type:message-id:cc:from:subject:date:to; bh=2TbIaAweLYMFMxiQbIs7WxRBesP/RdomHnuOdosPbWY=; b=XwgCOfB5RPGX6QRxCGJLS5UGQmjF1VE8BYd3ayoo9bsImFoFgkVR1hvpj+c97GldlL /IDKK4K+1GeNykmlxMhLFw3UxbDrr4XM3xo1nxHAiB4cZ33Sg/2y+8a5bG0/jK0aHfpS U8tanD56ysT+3ufmftxkR/WemFFyezGnYzPlD746xsR5JkFJILWxW6184oLO7FvO634s 7uwl19gi8QJdQtxchErKiP5jYkA5zDHK0Kb9zR5SiPS6dg0LNe/dFH4NOlHAwfPdp9qK icBViH4OqKjGYyJN2KJ8OdKBEQ12MrV3HGecYdVu49Wm6HZJUcmoUyKrHojJZfTvLUJs /gtg== X-Received: by 10.180.21.235 with SMTP id y11mr9860975wie.75.1406760652112; Wed, 30 Jul 2014 15:50:52 -0700 (PDT) Received: from [192.168.1.11] ([46.7.149.72]) by mx.google.com with ESMTPSA id bx2sm8860896wjb.47.2014.07.30.15.50.50 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Wed, 30 Jul 2014 15:50:50 -0700 (PDT) References: In-Reply-To: Mime-Version: 1.0 (iPhone Mail 8L1) Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary=Apple-Mail-20--1042143391 Message-Id: <1D742D88-394D-4B9D-ADF8-D0C832A5B16D@gmail.com> Cc: "pgsql-sql@postgresql.org" X-Mailer: iPhone Mail (8L1) From: Oliver d'Azevedo Christina Subject: Re: Reg: Sql Join Date: Thu, 31 Jul 2014 00:09:43 +0100 To: CrashBandi X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --Apple-Mail-20--1042143391 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I am not sure if I understand what you're trying to achieve.=20 It would help if you could provide an output example.=20 Best, Oliver=20 Sent via iPhone, apologies for any errors Em 30/07/2014, =C3=A0s 11:08 PM, CrashBandi escr= eveu: > Hi, >=20 > I am having the following question. I am not sure how to approach it. Plea= se help! >=20 > table A > name col1 col2 col3 col4 > apple 100 11111 1 APL > orange 200 22222 3 ORG > carrot 300 33333 3 CRT >=20 >=20 > table B > custom_name value obj_type obj_id > apple a FR 100 > orange o FR 200 > carrot c VG 300 > apple d FR 11111 > orange e VG 22222 > carrot f UC 33333 > apple h VG 1 > orange o FR 3 > carrot c VG 3 >=20 > when obj_type =3D'FR' then join on col1 > When obj_type=3D'VG' then join on col2 > When obj_type=3D'UC' then join on col2 >=20 > Thanks In advance, > CB > =20 --Apple-Mail-20--1042143391 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
I am not sure if I understand what you'= re trying to achieve. 
It would help if you could provide an o= utput example. 

Best,
Oliver 

Sent via iPhone, apologies for any errors

Em 30/0= 7/2014, =C3=A0s 11:08 PM, CrashBandi <crashbandicootu@gmail.com> escreveu:

Hi,

I am having the following question. I am not sure how to approach it= . Please help!

table A
name co= l1 co= l2 co= l3 co= l4
a= pple 100 11111 1 APL<= /td>
o= range 200 22222 3 ORG<= /td>
c= arrot 300 33333 3 CRT<= /td>


table B
custom_na= me value obj_type obj_id
apple a FR 100
orange o FR 200
carrot c VG 300
apple d FR 11111
orange e VG 22222
carrot f UC 33333
apple h VG 1
orange o FR 3
carrot c VG 3

when obj_type =3D'= FR' then join on col1
When obj_type=3D'VG' then join on col2
=
When obj_type=3D'UC' then join on col2

Thanks In advance,
CB
 
= --Apple-Mail-20--1042143391-- From GavinFlower@archidevsys.co.nz Wed Jul 30 23:34:32 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCdOB-00086d-VW for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 23:34:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XCdOB-0004Kv-7W for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 23:34:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XCdOA-0004Kn-Bn for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 23:34:30 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCdO3-0006z0-Us for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 23:34:28 +0000 Received: from [10.1.1.3] (121-99-185-67.bng1.nct.orcon.net.nz [121.99.185.67]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id s6UNY8NT009068 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES128-SHA bits=128 verify=NOT); Thu, 31 Jul 2014 11:34:14 +1200 Message-ID: <53D980F0.8020603@archidevsys.co.nz> Date: Thu, 31 Jul 2014 11:34:08 +1200 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.5.0 MIME-Version: 1.0 To: CrashBandi , pgsql-sql@postgresql.org Subject: Re: Reg: Sql Join References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------060607010502010307020509" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------060607010502010307020509 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 31/07/14 10:08, CrashBandi wrote: > table A > name col1 col2 col3 col4 > apple 100 11111 1 APL > orange 200 22222 3 ORG > carrot 300 33333 3 CRT > > > > table B > custom_name value obj_type obj_id > apple a FR 100 > orange o FR 200 > carrot c VG 300 > apple d FR 11111 > orange e VG 22222 > carrot f UC 33333 > apple h VG 1 > orange o FR 3 > carrot c VG 3 > Can't actually do joins the way you want but consider the following... DROP TABLE IF EXISTS table_a; DROP TABLE IF EXISTS table_b; CREATE TABLE table_a ( id SERIAL PRIMARY KEY, name text, col1 int, col2 int, col3 int, col4 text ); CREATE TABLE table_b ( id SERIAL PRIMARY KEY, custom_name text, value text, obj_type text, obj_id int ); INSERT INTO table_a (name, col1, col2, col3, col4) VALUES ('apple', 100, 11111, 1, 'APL'), ('orange', 200, 22222, 3, 'ORG'), ('carrot', 300, 33333, 3, 'CRT') /**/;/**/ INSERT INTO table_b (custom_name, value, obj_type, obj_id) VALUES ('apple', 'a', 'FR', 100), ('orange', 'o', 'FR', 200), ('carrot', 'c', 'VG', 300), ('apple', 'd', 'FR', 11111), ('orange', 'e', 'VG', 22222), ('carrot', 'f', 'UC', 33333), ('apple', 'h', 'VG', 1), ('orange', 'o', 'FR', 3), ('carrot', 'c', 'VG', 3) /**/;/**/ SELECT * FROM table_a a, table_b b WHERE ( obj_type ='FR' AND obj_id = col1 ) OR ( obj_type ='VG' AND obj_id = col2 ) OR ( obj_type ='UC' AND obj_id = col2 ); SELECT * FROM table_a a, table_b b WHERE obj_type ='FR' AND obj_id = col1 UNION SELECT * FROM table_a a, table_b b WHERE obj_type ='VG' AND obj_id = col2 UNION SELECT * FROM table_a a, table_b b WHERE obj_type ='UC' AND obj_id = col2 /**/;/**/ Cheers, Gavin --------------060607010502010307020509 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 31/07/14 10:08, CrashBandi wrote:
table A
name col1 col2 col3 col4
apple 100 11111 1 APL
orange 200 22222 3 ORG
carrot 300 33333 3 CRT


table B
custom_name value obj_type obj_id
apple a FR 100
orange o FR 200
carrot c VG 300
apple d FR 11111
orange e VG 22222
carrot f UC 33333
apple h VG 1
orange o FR 3
carrot c VG 3

Can't actually do joins the way you want but consider the following...

DROP TABLE IF EXISTS table_a;
DROP TABLE IF EXISTS table_b;


CREATE TABLE table_a
(
    id      SERIAL PRIMARY KEY,
    name    text,
    col1    int,
    col2    int,
    col3    int,
    col4    text   
);


CREATE TABLE table_b
(
    id          SERIAL PRIMARY KEY,
    custom_name text,
    value       text,
    obj_type    text,
    obj_id      int
);


INSERT INTO table_a
    (name, col1, col2, col3, col4)
VALUES
    ('apple', 100, 11111, 1, 'APL'),
    ('orange', 200, 22222, 3, 'ORG'),
    ('carrot', 300, 33333, 3, 'CRT')
/**/;/**/


INSERT INTO table_b
    (custom_name, value, obj_type, obj_id)
VALUES
    ('apple', 'a', 'FR', 100),
    ('orange', 'o', 'FR', 200),
    ('carrot', 'c', 'VG', 300),
    ('apple', 'd', 'FR', 11111),
    ('orange', 'e', 'VG', 22222),
    ('carrot', 'f', 'UC', 33333),
    ('apple', 'h', 'VG', 1),
    ('orange', 'o', 'FR', 3),
    ('carrot', 'c', 'VG', 3)
/**/;/**/


SELECT
    *
FROM
    table_a a,
    table_b b
WHERE
    (
        obj_type ='FR'
        AND
        obj_id = col1
    )
    OR   
    (
        obj_type ='VG'
        AND
        obj_id = col2
    )
    OR
    (
        obj_type ='UC'
        AND
        obj_id = col2
    );


SELECT
    *
FROM
    table_a a,
    table_b b
WHERE
        obj_type ='FR'
    AND obj_id = col1
UNION
SELECT
    *
FROM
    table_a a,
    table_b b
WHERE
        obj_type ='VG'
    AND obj_id = col2
UNION
SELECT
    *
FROM
    table_a a,
    table_b b
WHERE
        obj_type ='UC'
    AND obj_id = col2
/**/;/**/


Cheers,
Gavin
--------------060607010502010307020509-- From GavinFlower@archidevsys.co.nz Wed Jul 30 23:43:25 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCdWm-0000An-QZ for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 23:43:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XCdWm-0000vB-7o for pgsql-sql@arkaria.postgresql.org; Wed, 30 Jul 2014 23:43:24 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XCdWk-0000uS-L5 for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 23:43:22 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCdWe-0007Zt-Us for pgsql-sql@postgresql.org; Wed, 30 Jul 2014 23:43:20 +0000 Received: from [10.1.1.3] (121-99-185-67.bng1.nct.orcon.net.nz [121.99.185.67]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id s6UNhAau009468 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES128-SHA bits=128 verify=NOT); Thu, 31 Jul 2014 11:43:10 +1200 Message-ID: <53D9830E.9090003@archidevsys.co.nz> Date: Thu, 31 Jul 2014 11:43:10 +1200 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.5.0 MIME-Version: 1.0 To: CrashBandi , pgsql-sql@postgresql.org Subject: Re: Reg: Sql Join References: <53D980F0.8020603@archidevsys.co.nz> In-Reply-To: <53D980F0.8020603@archidevsys.co.nz> Content-Type: multipart/alternative; boundary="------------090106020409010804010002" X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------090106020409010804010002 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 31/07/14 11:34, Gavin Flower wrote: > On 31/07/14 10:08, CrashBandi wrote: >> table A >> name col1 col2 col3 col4 >> apple 100 11111 1 APL >> orange 200 22222 3 ORG >> carrot 300 33333 3 CRT >> >> >> >> table B >> custom_name value obj_type obj_id >> apple a FR 100 >> orange o FR 200 >> carrot c VG 300 >> apple d FR 11111 >> orange e VG 22222 >> carrot f UC 33333 >> apple h VG 1 >> orange o FR 3 >> carrot c VG 3 >> > [...] Better style, is to prefix the columns with a table alias (though it makes no logical difference in this case!). I have also added the output, using psql. DROP TABLE IF EXISTS table_a; DROP TABLE IF EXISTS table_b; CREATE TABLE table_a ( id SERIAL PRIMARY KEY, name text, col1 int, col2 int, col3 int, col4 text ); CREATE TABLE table_b ( id SERIAL PRIMARY KEY, custom_name text, value text, obj_type text, obj_id int ); INSERT INTO table_a (name, col1, col2, col3, col4) VALUES ('apple', 100, 11111, 1, 'APL'), ('orange', 200, 22222, 3, 'ORG'), ('carrot', 300, 33333, 3, 'CRT') /**/;/**/ INSERT INTO table_b (custom_name, value, obj_type, obj_id) VALUES ('apple', 'a', 'FR', 100), ('orange', 'o', 'FR', 200), ('carrot', 'c', 'VG', 300), ('apple', 'd', 'FR', 11111), ('orange', 'e', 'VG', 22222), ('carrot', 'f', 'UC', 33333), ('apple', 'h', 'VG', 1), ('orange', 'o', 'FR', 3), ('carrot', 'c', 'VG', 3) /**/;/**/ SELECT * FROM table_a a, table_b b WHERE ( b.obj_type ='FR' AND b.obj_id = a.col1 ) OR ( b.obj_type ='VG' AND b.obj_id = a.col2 ) OR ( b.obj_type ='UC' AND b.obj_id = a.col2 ); SELECT * FROM table_a a, table_b b WHERE b.obj_type ='FR' AND b.obj_id = a.col1 UNION SELECT * FROM table_a a, table_b b WHERE b.obj_type ='VG' AND b.obj_id = a.col2 UNION SELECT * FROM table_a a, table_b b WHERE b.obj_type ='UC' AND b.obj_id = a.col2 /**/;/**/ $ psql Password: psql (9.2.8) Type "help" for help. gavin=> \i SQL.sql DROP TABLE DROP TABLE psql:SQL.sql:14: NOTICE: CREATE TABLE will create implicit sequence "table_a_id_seq" for serial column "table_a.id" psql:SQL.sql:14: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "table_a_pkey" for table "table_a" CREATE TABLE psql:SQL.sql:24: NOTICE: CREATE TABLE will create implicit sequence "table_b_id_seq" for serial column "table_b.id" psql:SQL.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "table_b_pkey" for table "table_b" CREATE TABLE INSERT 0 3 INSERT 0 9 id | name | col1 | col2 | col3 | col4 | id | custom_name | value | obj_type | obj_id ----+--------+------+-------+------+------+----+-------------+-------+----------+-------- 1 | apple | 100 | 11111 | 1 | APL | 1 | apple | a | FR | 100 2 | orange | 200 | 22222 | 3 | ORG | 2 | orange | o | FR | 200 2 | orange | 200 | 22222 | 3 | ORG | 5 | orange | e | VG | 22222 3 | carrot | 300 | 33333 | 3 | CRT | 6 | carrot | f | UC | 33333 (4 rows) id | name | col1 | col2 | col3 | col4 | id | custom_name | value | obj_type | obj_id ----+--------+------+-------+------+------+----+-------------+-------+----------+-------- 3 | carrot | 300 | 33333 | 3 | CRT | 6 | carrot | f | UC | 33333 2 | orange | 200 | 22222 | 3 | ORG | 5 | orange | e | VG | 22222 1 | apple | 100 | 11111 | 1 | APL | 1 | apple | a | FR | 100 2 | orange | 200 | 22222 | 3 | ORG | 2 | orange | o | FR | 200 (4 rows) gavin=> Cheers, Gavin --------------090106020409010804010002 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 31/07/14 11:34, Gavin Flower wrote:
On 31/07/14 10:08, CrashBandi wrote:
table A
name col1 col2 col3 col4
apple 100 11111 1 APL
orange 200 22222 3 ORG
carrot 300 33333 3 CRT


table B
custom_name value obj_type obj_id
apple a FR 100
orange o FR 200
carrot c VG 300
apple d FR 11111
orange e VG 22222
carrot f UC 33333
apple h VG 1
orange o FR 3
carrot c VG 3

[...]

Better style, is to prefix the columns with a table alias (though it makes no logical difference in this case!).

I have also added the output, using psql.

DROP TABLE IF EXISTS table_a;
DROP TABLE IF EXISTS table_b;


CREATE TABLE table_a
(
    id      SERIAL PRIMARY KEY,
    name    text,
    col1    int,
    col2    int,
    col3    int,
    col4    text   
);


CREATE TABLE table_b
(
    id          SERIAL PRIMARY KEY,
    custom_name text,
    value       text,
    obj_type    text,
    obj_id      int
);


INSERT INTO table_a
    (name, col1, col2, col3, col4)
VALUES
    ('apple', 100, 11111, 1, 'APL'),
    ('orange', 200, 22222, 3, 'ORG'),
    ('carrot', 300, 33333, 3, 'CRT')
/**/;/**/


INSERT INTO table_b
    (custom_name, value, obj_type, obj_id)
VALUES
    ('apple', 'a', 'FR', 100),
    ('orange', 'o', 'FR', 200),
    ('carrot', 'c', 'VG', 300),
    ('apple', 'd', 'FR', 11111),
    ('orange', 'e', 'VG', 22222),
    ('carrot', 'f', 'UC', 33333),
    ('apple', 'h', 'VG', 1),
    ('orange', 'o', 'FR', 3),
    ('carrot', 'c', 'VG', 3)
/**/;/**/


SELECT
    *
FROM
    table_a a,
    table_b b
WHERE
    (
        b.obj_type ='FR'
        AND
        b.obj_id = a.col1
    )
    OR   
    (
        b.obj_type ='VG'
        AND
        b.obj_id = a.col2
    )
    OR
    (
        b.obj_type ='UC'
        AND
        b.obj_id = a.col2
    );


SELECT
    *
FROM
    table_a a,
    table_b b
WHERE
        b.obj_type ='FR'
    AND b.obj_id = a.col1
UNION
SELECT
    *
FROM
    table_a a,
    table_b b
WHERE
        b.obj_type ='VG'
    AND b.obj_id = a.col2
UNION
SELECT
    *
FROM
    table_a a,
    table_b b
WHERE
        b.obj_type ='UC'
    AND b.obj_id = a.col2
/**/;/**/


$ psql
Password:
psql (9.2.8)
Type "help" for help.

gavin=> \i SQL.sql
DROP TABLE
DROP TABLE
psql:SQL.sql:14: NOTICE:  CREATE TABLE will create implicit sequence "table_a_id_seq" for serial column "table_a.id"
psql:SQL.sql:14: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table_a_pkey" for table "table_a"
CREATE TABLE
psql:SQL.sql:24: NOTICE:  CREATE TABLE will create implicit sequence "table_b_id_seq" for serial column "table_b.id"
psql:SQL.sql:24: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table_b_pkey" for table "table_b"
CREATE TABLE
INSERT 0 3
INSERT 0 9
 id |  name  | col1 | col2  | col3 | col4 | id | custom_name | value | obj_type | obj_id
----+--------+------+-------+------+------+----+-------------+-------+----------+--------
  1 | apple  |  100 | 11111 |    1 | APL  |  1 | apple       | a     | FR       |    100
  2 | orange |  200 | 22222 |    3 | ORG  |  2 | orange      | o     | FR       |    200
  2 | orange |  200 | 22222 |    3 | ORG  |  5 | orange      | e     | VG       |  22222
  3 | carrot |  300 | 33333 |    3 | CRT  |  6 | carrot      | f     | UC       |  33333
(4 rows)

 id |  name  | col1 | col2  | col3 | col4 | id | custom_name | value | obj_type | obj_id
----+--------+------+-------+------+------+----+-------------+-------+----------+--------
  3 | carrot |  300 | 33333 |    3 | CRT  |  6 | carrot      | f     | UC       |  33333
  2 | orange |  200 | 22222 |    3 | ORG  |  5 | orange      | e     | VG       |  22222
  1 | apple  |  100 | 11111 |    1 | APL  |  1 | apple       | a     | FR       |    100
  2 | orange |  200 | 22222 |    3 | ORG  |  2 | orange      | o     | FR       |    200
(4 rows)

gavin=>

Cheers,
Gavin

--------------090106020409010804010002-- From crashbandicootu@gmail.com Thu Jul 31 19:35:16 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XCw8C-0002do-9G for pgsql-sql@arkaria.postgresql.org; Thu, 31 Jul 2014 19:35:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XCw8B-0006yH-N1 for pgsql-sql@arkaria.postgresql.org; Thu, 31 Jul 2014 19:35:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XCw89-0006v0-Fs for pgsql-sql@postgresql.org; Thu, 31 Jul 2014 19:35:13 +0000 Received: from mail-ig0-x242.google.com ([2607:f8b0:4001:c05::242]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XCw7y-0004Vk-JU for pgsql-sql@postgresql.org; Thu, 31 Jul 2014 19:35:12 +0000 Received: by mail-ig0-f194.google.com with SMTP id r2so38568igi.5 for ; Thu, 31 Jul 2014 12:34:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=JM+hBL7PD3NB6f7nSKhRGTwrcyUQS7vmqbAc8KkchUc=; b=ZgXxN59L9kxptP5vCSX3exKZD/FisJDSPglEkQ/lcG5as9sTMPWWWOdQdcbhJv0gPc LQTUflSpCp3y0mobZWvlQw4CsPSMy3OFbiKFpP2xWRMR0jkrGyCX98Zxet01f/ZFSJjR tmhUgTeVCoUxaqqTMw0deCR056qo0f65JWkZl4cAG+rPgMAR9385fvLt9uHRR18MaAeJ I6oS1/MFUny46XzDuZv4JFwGmrDuuKmT6V6CoVdTv8mPxbOF2CPJGTj16vLaOjMgkOx5 mjFIYD8IqPkPpeBMLULKpw5pquedF4xIxbhBzVGKaQBTkGxTZLm+Oa8dB42PpDbpxUjs JnKQ== MIME-Version: 1.0 X-Received: by 10.50.124.227 with SMTP id ml3mr547519igb.46.1406835299848; Thu, 31 Jul 2014 12:34:59 -0700 (PDT) Received: by 10.107.160.11 with HTTP; Thu, 31 Jul 2014 12:34:59 -0700 (PDT) In-Reply-To: <53D9830E.9090003@archidevsys.co.nz> References: <53D980F0.8020603@archidevsys.co.nz> <53D9830E.9090003@archidevsys.co.nz> Date: Thu, 31 Jul 2014 12:34:59 -0700 Message-ID: Subject: Re: Reg: Sql Join From: CrashBandi To: Gavin Flower Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001a1134c43c4e19f604ff825edb X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --001a1134c43c4e19f604ff825edb Content-Type: text/plain; charset=UTF-8 Hi Gavin, Thank u very much.. On Wed, Jul 30, 2014 at 4:43 PM, Gavin Flower wrote: > On 31/07/14 11:34, Gavin Flower wrote: > > On 31/07/14 10:08, CrashBandi wrote: > > table A > name col1 col2 col3 col4 apple 100 11111 1 APL orange 200 22222 3 ORG > carrot 300 33333 3 CRT > > table B > custom_name value obj_type obj_id apple a FR 100 orange o FR 200 > carrot c VG 300 apple d FR 11111 orange e VG 22222 carrot f UC 33333 > apple h VG 1 orange o FR 3 carrot c VG 3 > > > [...] > > Better style, is to prefix the columns with a table alias (though it makes > no logical difference in this case!). > > I have also added the output, using psql. > > > DROP TABLE IF EXISTS table_a; > DROP TABLE IF EXISTS table_b; > > > CREATE TABLE table_a > ( > id SERIAL PRIMARY KEY, > name text, > col1 int, > col2 int, > col3 int, > col4 text > ); > > > CREATE TABLE table_b > ( > id SERIAL PRIMARY KEY, > custom_name text, > value text, > obj_type text, > obj_id int > ); > > > INSERT INTO table_a > (name, col1, col2, col3, col4) > VALUES > ('apple', 100, 11111, 1, 'APL'), > ('orange', 200, 22222, 3, 'ORG'), > ('carrot', 300, 33333, 3, 'CRT') > /**/;/**/ > > > INSERT INTO table_b > (custom_name, value, obj_type, obj_id) > VALUES > ('apple', 'a', 'FR', 100), > ('orange', 'o', 'FR', 200), > ('carrot', 'c', 'VG', 300), > ('apple', 'd', 'FR', 11111), > ('orange', 'e', 'VG', 22222), > ('carrot', 'f', 'UC', 33333), > ('apple', 'h', 'VG', 1), > ('orange', 'o', 'FR', 3), > ('carrot', 'c', 'VG', 3) > /**/;/**/ > > > SELECT > * > FROM > table_a a, > table_b b > WHERE > ( > b.obj_type ='FR' > AND > b.obj_id = a.col1 > ) > OR > ( > b.obj_type ='VG' > AND > b.obj_id = a.col2 > ) > OR > ( > b.obj_type ='UC' > AND > b.obj_id = a.col2 > > ); > > > SELECT > * > FROM > table_a a, > table_b b > WHERE > b.obj_type ='FR' > AND b.obj_id = a.col1 > > UNION > SELECT > * > FROM > table_a a, > table_b b > WHERE > b.obj_type ='VG' > AND b.obj_id = a.col2 > > UNION > SELECT > * > FROM > table_a a, > table_b b > WHERE > b.obj_type ='UC' > AND b.obj_id = a.col2 > /**/;/**/ > > > $ psql > Password: > psql (9.2.8) > Type "help" for help. > > gavin=> \i SQL.sql > DROP TABLE > DROP TABLE > psql:SQL.sql:14: NOTICE: CREATE TABLE will create implicit sequence > "table_a_id_seq" for serial column "table_a.id" > psql:SQL.sql:14: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit > index "table_a_pkey" for table "table_a" > CREATE TABLE > psql:SQL.sql:24: NOTICE: CREATE TABLE will create implicit sequence > "table_b_id_seq" for serial column "table_b.id" > psql:SQL.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit > index "table_b_pkey" for table "table_b" > CREATE TABLE > INSERT 0 3 > INSERT 0 9 > id | name | col1 | col2 | col3 | col4 | id | custom_name | value | > obj_type | obj_id > > ----+--------+------+-------+------+------+----+-------------+-------+----------+-------- > 1 | apple | 100 | 11111 | 1 | APL | 1 | apple | a | > FR | 100 > 2 | orange | 200 | 22222 | 3 | ORG | 2 | orange | o | > FR | 200 > 2 | orange | 200 | 22222 | 3 | ORG | 5 | orange | e | > VG | 22222 > 3 | carrot | 300 | 33333 | 3 | CRT | 6 | carrot | f | > UC | 33333 > (4 rows) > > id | name | col1 | col2 | col3 | col4 | id | custom_name | value | > obj_type | obj_id > > ----+--------+------+-------+------+------+----+-------------+-------+----------+-------- > 3 | carrot | 300 | 33333 | 3 | CRT | 6 | carrot | f | > UC | 33333 > 2 | orange | 200 | 22222 | 3 | ORG | 5 | orange | e | > VG | 22222 > 1 | apple | 100 | 11111 | 1 | APL | 1 | apple | a | > FR | 100 > 2 | orange | 200 | 22222 | 3 | ORG | 2 | orange | o | > FR | 200 > (4 rows) > > gavin=> > > > Cheers, > Gavin > > --001a1134c43c4e19f604ff825edb Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Gavin,

Thank u very much..


On Wed, Jul= 30, 2014 at 4:43 PM, Gavin Flower <GavinFlower@archidevsys.co= .nz> wrote:
=20 =20 =20
On 31/07/14 11:34, Gavin Flower wrote:
=20
On 31/07/14 10:08, CrashBandi wrote:
table A
name = col1 = col2 = col3 = col4
apple 100 11111 1 AP= L
orange 200 22222 3 OR= G
carrot 300 33333 3 CR= T


table B
custom_name value obj_type obj_id
apple a FR 100
orange o FR 200
carrot c VG 300
apple d FR 11111
orange e VG 22222
carrot f UC 33333
apple h VG 1
orange o FR 3
carrot c VG 3

[...]

Better style, is to prefix the columns with a table alias (though it makes no logical difference in this case!).

I have also added the output, using psql.

DROP TABLE IF EXISTS table_a;
DROP TABLE IF EXISTS table_b;


CREATE TABLE table_a
(=
=C2=A0= =C2=A0=C2=A0 id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SERIAL PRIMARY KEY,
=C2=A0= =C2=A0=C2=A0 name=C2=A0=C2=A0=C2=A0 text,
=C2=A0= =C2=A0=C2=A0 col1=C2=A0=C2=A0=C2=A0 int,
=C2=A0= =C2=A0=C2=A0 col2=C2=A0=C2=A0=C2=A0 int,
=C2=A0= =C2=A0=C2=A0 col3=C2=A0=C2=A0=C2=A0 int,
=C2=A0= =C2=A0=C2=A0 col4=C2=A0=C2=A0=C2=A0 text=C2=A0=C2=A0=C2=A0
);


CREATE TABLE table_b
(=
=C2=A0= =C2=A0=C2=A0 id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SERIAL= PRIMARY KEY,
=C2=A0= =C2=A0=C2=A0 custom_name text,
=C2=A0= =C2=A0=C2=A0 value=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 text,<= /small>
=C2=A0= =C2=A0=C2=A0 obj_type=C2=A0=C2=A0=C2=A0 text,
=C2=A0= =C2=A0=C2=A0 obj_id=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 int=
);


INSERT INTO table_a
=C2=A0= =C2=A0=C2=A0 (name, col1, col2, col3, col4)
VALUES
=C2=A0= =C2=A0=C2=A0 ('apple', 100, 11111, 1, 'APL'),=
=C2=A0= =C2=A0=C2=A0 ('orange', 200, 22222, 3, 'ORG'),
=C2=A0= =C2=A0=C2=A0 ('carrot', 300, 33333, 3, 'CRT')=
/**/;/**= /


INSERT INTO table_b
=C2=A0= =C2=A0=C2=A0 (custom_name, value, obj_type, obj_id) VALUES
=C2=A0= =C2=A0=C2=A0 ('apple', 'a', 'FR', 100),
=C2=A0= =C2=A0=C2=A0 ('orange', 'o', 'FR', 200),
=C2=A0= =C2=A0=C2=A0 ('carrot', 'c', 'VG', 300),
=C2=A0= =C2=A0=C2=A0 ('apple', 'd', 'FR', 11111),
=C2=A0= =C2=A0=C2=A0 ('orange', 'e', 'VG', 22222),
=C2=A0= =C2=A0=C2=A0 ('carrot', 'f', 'UC', 33333),
=C2=A0= =C2=A0=C2=A0 ('apple', 'h', 'VG', 1),=
=C2=A0= =C2=A0=C2=A0 ('orange', 'o', 'FR', 3),
=C2=A0= =C2=A0=C2=A0 ('carrot', 'c', 'VG', 3)=
/**/;/**= /


SELECT
=C2=A0= =C2=A0=C2=A0 *
FROM
=C2=A0= =C2=A0=C2=A0 table_a a,
=C2=A0= =C2=A0=C2=A0 table_b b
WHERE
=C2=A0= =C2=A0=C2=A0 (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b.obj_type =3D'FR'
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b.obj_id =3D a.col1
=C2=A0= =C2=A0=C2=A0 )
=C2=A0= =C2=A0=C2=A0 OR=C2=A0=C2=A0=C2=A0
=C2=A0= =C2=A0=C2=A0 (
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b.obj_type =3D'VG'
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b.obj_id =3D a.col2
=C2=A0= =C2=A0=C2=A0 )
=C2=A0= =C2=A0=C2=A0 OR
=C2=A0= =C2=A0=C2=A0 (
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b.obj_type =3D'UC'
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND
=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b.obj_id =3D a.col2

=C2=A0= =C2=A0=C2=A0 );


SELECT
=C2=A0= =C2=A0=C2=A0 *
FROM
=C2=A0= =C2=A0=C2=A0 table_a a,
=C2=A0= =C2=A0=C2=A0 table_b b
WHERE
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b.obj_type =3D'FR'
=C2=A0= =C2=A0=C2=A0 AND b.obj_id =3D a.col1

UNION
SELECT
=C2=A0= =C2=A0=C2=A0 *
FROM
=C2=A0= =C2=A0=C2=A0 table_a a,
=C2=A0= =C2=A0=C2=A0 table_b b
WHERE
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b.obj_type =3D'VG'
=C2=A0= =C2=A0=C2=A0 AND b.obj_id =3D a.col2

UNION
SELECT
=C2=A0= =C2=A0=C2=A0 *
FROM
=C2=A0= =C2=A0=C2=A0 table_a a,
=C2=A0= =C2=A0=C2=A0 table_b b
WHERE
= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 b.obj_type =3D'UC'
=C2=A0= =C2=A0=C2=A0 AND b.obj_id =3D a.col2
/**/;/**= /


$ psql
Password= :
psql (9.2.8)
Type "help" for help.

gavin=3D= > \i SQL.sql
DROP TABLE
DROP TABLE
psql:SQL= .sql:14: NOTICE:=C2=A0 CREATE TABLE will create implicit sequence "table_a_id_seq" for serial column "table_a.id"
psql:SQL= .sql:14: NOTICE:=C2=A0 CREATE TABLE / PRIMARY KEY will create implicit index "table_a_pkey" for table "table_a"
CREATE TABLE
psql:SQL= .sql:24: NOTICE:=C2=A0 CREATE TABLE will create implicit sequence "table_b_id_seq" for serial column "table_b.id"
psql:SQL= .sql:24: NOTICE:=C2=A0 CREATE TABLE / PRIMARY KEY will create implicit index "table_b_pkey" for table "table_b"
CREATE TABLE
INSERT 0 3
INSERT 0 9
=C2=A0id= |=C2=A0 name=C2=A0 | col1 | col2=C2=A0 | col3 | col4 | id | custom_name= | value | obj_type | obj_id
----+---= -----+------+-------+------+------+----+-------------+-------+----------+--= ------
=C2=A0 1= | apple=C2=A0 |=C2=A0 100 | 11111 |=C2=A0=C2=A0=C2=A0 1 | APL=C2= =A0 |=C2=A0 1 | apple=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | a=C2=A0=C2=A0=C2=A0=C2=A0 | FR=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 |=C2=A0=C2=A0=C2=A0 100
=C2=A0 2= | orange |=C2=A0 200 | 22222 |=C2=A0=C2=A0=C2=A0 3 | ORG=C2=A0 |= =C2=A0 2 | orange=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | o=C2=A0=C2=A0=C2=A0=C2=A0 | FR=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 |=C2=A0=C2=A0=C2=A0 200
=C2=A0 2= | orange |=C2=A0 200 | 22222 |=C2=A0=C2=A0=C2=A0 3 | ORG=C2=A0 |= =C2=A0 5 | orange=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | e=C2=A0=C2=A0=C2=A0=C2=A0 | VG=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 |=C2=A0 22222
=C2=A0 3= | carrot |=C2=A0 300 | 33333 |=C2=A0=C2=A0=C2=A0 3 | CRT=C2=A0 |= =C2=A0 6 | carrot=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | f=C2=A0=C2=A0=C2=A0=C2=A0 | UC=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 |=C2=A0 33333
(4 rows)

=C2=A0id= |=C2=A0 name=C2=A0 | col1 | col2=C2=A0 | col3 | col4 | id | custom_name= | value | obj_type | obj_id
----+---= -----+------+-------+------+------+----+-------------+-------+----------+--= ------
=C2=A0 3= | carrot |=C2=A0 300 | 33333 |=C2=A0=C2=A0=C2=A0 3 | CRT=C2=A0 |= =C2=A0 6 | carrot=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | f=C2=A0=C2=A0=C2=A0=C2=A0 | UC=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 |=C2=A0 33333
=C2=A0 2= | orange |=C2=A0 200 | 22222 |=C2=A0=C2=A0=C2=A0 3 | ORG=C2=A0 |= =C2=A0 5 | orange=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | e=C2=A0=C2=A0=C2=A0=C2=A0 | VG=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 |=C2=A0 22222
=C2=A0 1= | apple=C2=A0 |=C2=A0 100 | 11111 |=C2=A0=C2=A0=C2=A0 1 | APL=C2= =A0 |=C2=A0 1 | apple=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | a=C2=A0=C2=A0=C2=A0=C2=A0 | FR=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 |=C2=A0=C2=A0=C2=A0 100
=C2=A0 2= | orange |=C2=A0 200 | 22222 |=C2=A0=C2=A0=C2=A0 3 | ORG=C2=A0 |= =C2=A0 2 | orange=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | o=C2=A0=C2=A0=C2=A0=C2=A0 | FR=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 |=C2=A0=C2=A0=C2=A0 200
(4 rows)

gavin=3D= >

Cheers,
Gavin


--001a1134c43c4e19f604ff825edb--