Re: Reg: Sql Join - Mailing list pgsql-sql

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