Re: Reg: Sql Join

Hi Gavin,

Thank u very much..

On Wed, Jul 30, 2014 at 4:43 PM, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz
> 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
>
>