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