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--