agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: CrashBandi <crashbandicootu@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Reg: Sql Join
Date: Thu, 31 Jul 2014 11:34:08 +1200
Message-ID: <53D980F0.8020603@archidevsys.co.nz> (raw)
In-Reply-To: <CAAvgTTT02zxdRzfcFiq+V-zDQzcOUNrsQGSLNsi2Xc02kuygKg@mail.gmail.com>
References: <CAAvgTTT02zxdRzfcFiq+V-zDQzcOUNrsQGSLNsi2Xc02kuygKg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (7+ messages)  latest in thread

Message-ID: <53D980F0.8020603@archidevsys.co.nz>
Permalink:  ../53D980F0.8020603@archidevsys.co.nz/
Also on:    postgresql.org/message-id/53D980F0.8020603@archidevsys.co.nz

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: GavinFlower@archidevsys.co.nz, crashbandicootu@gmail.com
  Subject: Re: Reg: Sql Join
  In-Reply-To: <53D980F0.8020603@archidevsys.co.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox