agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedReg: Sql Join
7+ messages / 4 participants
[nested] [flat]
* Reg: Sql Join
@ 2014-07-30 22:08 CrashBandi <crashbandicootu@gmail.com>
0 siblings, 3 replies; 7+ messages in thread
From: CrashBandi @ 2014-07-30 22:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reg: Sql Join
@ 2014-07-30 22:45 David G Johnston <david.g.johnston@gmail.com>
parent: CrashBandi <crashbandicootu@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: David G Johnston @ 2014-07-30 22:45 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reg: Sql Join
@ 2014-07-30 22:49 David G Johnston <david.g.johnston@gmail.com>
parent: David G Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: David G Johnston @ 2014-07-30 22:49 UTC (permalink / raw)
To: pgsql-sql
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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reg: Sql Join
@ 2014-07-30 23:09 Oliver d'Azevedo Christina <oliveiros.cristina@gmail.com>
parent: CrashBandi <crashbandicootu@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Oliver d'Azevedo Christina @ 2014-07-30 23:09 UTC (permalink / raw)
To: CrashBandi <crashbandicootu@gmail.com>; +Cc: pgsql-sql
I am not sure if I understand what you're trying to achieve.
It would help if you could provide an output example.
Best,
Oliver
Sent via iPhone, apologies for any errors
Em 30/07/2014, às 11:08 PM, CrashBandi <crashbandicootu@gmail.com> escreveu:
> 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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reg: Sql Join
@ 2014-07-30 23:34 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: CrashBandi <crashbandicootu@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Gavin Flower @ 2014-07-30 23:34 UTC (permalink / raw)
To: CrashBandi <crashbandicootu@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reg: Sql Join
@ 2014-07-30 23:43 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 1 reply; 7+ messages in thread
From: Gavin Flower @ 2014-07-30 23:43 UTC (permalink / raw)
To: CrashBandi <crashbandicootu@gmail.com>; 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reg: Sql Join
@ 2014-07-31 19:34 CrashBandi <crashbandicootu@gmail.com>
parent: Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 0 replies; 7+ messages in thread
From: CrashBandi @ 2014-07-31 19:34 UTC (permalink / raw)
To: Gavin Flower <GavinFlower@archidevsys.co.nz>; +Cc: pgsql-sql
Hi Gavin,
Thank u very much..
On Wed, Jul 30, 2014 at 4:43 PM, Gavin Flower <GavinFlower@archidevsys.co.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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2014-07-31 19:34 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-07-30 22:08 Reg: Sql Join CrashBandi <crashbandicootu@gmail.com>
2014-07-30 22:45 ` David G Johnston <david.g.johnston@gmail.com>
2014-07-30 22:49 ` David G Johnston <david.g.johnston@gmail.com>
2014-07-30 23:09 ` Oliver d'Azevedo Christina <oliveiros.cristina@gmail.com>
2014-07-30 23:34 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2014-07-30 23:43 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2014-07-31 19:34 ` CrashBandi <crashbandicootu@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox