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

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