agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: CrashBandi <crashbandicootu@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Reg: Sql Join
Date: Thu, 31 Jul 2014 11:43:10 +1200
Message-ID: <53D9830E.9090003@archidevsys.co.nz> (raw)
In-Reply-To: <53D980F0.8020603@archidevsys.co.nz>
References: <CAAvgTTT02zxdRzfcFiq+V-zDQzcOUNrsQGSLNsi2Xc02kuygKg@mail.gmail.com>
<53D980F0.8020603@archidevsys.co.nz>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
view thread (7+ messages) latest in thread
Message-ID: <53D9830E.9090003@archidevsys.co.nz>
Permalink: ../53D9830E.9090003@archidevsys.co.nz/
Also on: postgresql.org/message-id/53D9830E.9090003@archidevsys.co.nz
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, crashbandicootu@gmail.com
Subject: Re: Reg: Sql Join
In-Reply-To: <53D9830E.9090003@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox