Thread: Reg: Sql Join

CrashBandi wrote
> Hi,
> 
> I am having the following question. I am not sure how to approach it.
> Please help!
> 
> 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
> when obj_type ='FR' then join on col1
> When obj_type='VG' then join on col2
> When obj_type='UC' then join on col2
> 
> Thanks In advance,
> CB

You cannot do conditional joins in this manner.  You will need to write
three joins, one each against a subquery with the appropriate where clause.

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/Reg-Sql-Join-tp5813360p5813363.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

David G Johnston wrote
> 
> CrashBandi wrote
>> Hi,
>> 
>> I am having the following question. I am not sure how to approach it.
>> Please help!
>> 
>> 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
>> when obj_type ='FR' then join on col1
>> When obj_type='VG' then join on col2
>> When obj_type='UC' then join on col2
>> 
>> Thanks In advance,
>> CB
> You cannot do conditional joins in this manner.  You will need to write
> three joins, one each against a subquery with the appropriate where
> clause.
> 
> David J.

Actually you might be able to do:

On (case obj_type when 'xxx' then col1 when 'yyy' then col2 end = obj_id) 

But I haven't tried something like this before.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Reg-Sql-Join-tp5813360p5813364.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Hi,

I am having the following question. I am not sure how to approach it. Please help!

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

when obj_type ='FR' then join on col1

When obj_type='VG' then join on col2

When obj_type='UC' then join on col2

Thanks In advance,

CB

 

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

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