PL> ------------------------------------------------------------
PL> -- QUERY
PL> ------------------------------------------------------------
PL> SELECT
PL> master_name,
PL> detail_name,
PL> type

PL> FROM
PL> master INNER JOIN detail
PL> ON master.id = detail.id_master

PL> INNER JOIN type
PL> ON detail.code_type = type.code

PL> ORDER by master.id, detail.id;

PL> ------------------------------------------------------------


PL> The result of that is:

PL> ----------------------------------
PL> master_name | detail_name | type |
PL> ----------------------------------
PL> M1 | M1, D1 | TA |
PL> M1 | M1, D2 | TB |
PL> M1 | M1, D3 | TA |
PL> M1 | M1, D4 | TC |
PL> M2 | M2, D1 | TC |
PL> M3 | M3, D1 | TA |
PL> M3 | M3, D2 | TA |
PL> M3 | M3, D3 | TB |
PL> M3 | M3, D4 | TA |
PL> M3 | M3, D5 | TB |
PL> M3 | M3, D6 | TC |
PL> M3 | M3, D7 | TC |
PL> ----------------------------------


PL> I need something like this:

PL> ----------------------------------------
PL> master_name | TA | TB | TC |
PL> ----------------------------------------
PL> M1 | M1, D1 | | |
PL> M1 | | M1, D2 | |
PL> M1 | M1, D3 | | |
PL> M1 | | | M1, D4 |
PL> M2 | | | M2, D1 |
PL> M3 | M3, D1 | | |
PL> M3 | M3, D2 | | |
PL> M3 | | M3, D3 | |
PL> M3 | M3, D4 | | |
PL> M3 | | M3, D5 | |
PL> M3 | | | M3, D6 |
PL> M3 | | | M3, D7 |
PL> ----------------------------------------


PL> Does anyone know how to do that in Postgresql? I run version 7.3.4.

PL> Thanks for any idea you might have.

PL> Philippe Lang

Maybe you can use the CASE construct.

select mastername, case when type='TA' then detail_name else '' end as ta, case .... as tb, case ... as ts from ...

I don't try this, but maybe...

DAQ


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Thu, Feb 19, 2004 at 06:23:53PM +0100, daq wrote:

drop table master cascade;
create table master (
id int4 UNIQUE,
master_name varchar(10)
);

drop table type cascade;
create table type (
code serial unique,
type varchar(10)
);

drop table detail cascade;
create table detail (
id serial unique,
master_id int4 REFERENCES master(id),
detail_name varchar(10),
type_code int4 REFERENCES type(code)
);


insert into master (id, master_name) values ('1','M1');
insert into master (id, master_name) values ('2','M2');
insert into master (id, master_name) values ('3','M3');

insert into type (code,type) values (1,'TA');
insert into type (code,type) values (2,'TB');
insert into type (code,type) values (3,'TC');

insert into detail (master_id, detail_name, type_code) values ('1','M1,D1',1);
insert into detail (master_id, detail_name, type_code) values ('1','M1,D3',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D1',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D3',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D4',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D5',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D6',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D8',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D7',1);
insert into detail (master_id, detail_name, type_code) values ('1','M1,D2',2);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D2',2);
insert into detail (master_id, detail_name, type_code) values (2,'M2,D4',3);
insert into detail (master_id, type_code) values (2,3);
insert into detail (master_id, type_code) values (1,3);
insert into detail (master_id, detail_name, type_code) values (2,'M2,D4',3);

drop view TA;
drop view TB;
drop view TC;

CREATE VIEW TA AS SELECT detail.id AS detail_id, detail_name AS TA, master.id AS master_id FROM master,detail WHERE detail.type_code=(SELECT code FROM type WHERE type.type = 'TA') AND detail.master_id=master.id;
SELECT * FROM TA;
CREATE VIEW TB AS SELECT detail.id AS detail_id, detail_name AS TB, master.id AS master_id FROM master,detail WHERE detail.type_code=(SELECT code FROM type WHERE type.type = 'TB') AND detail.master_id=master.id;
SELECT * FROM TB;
CREATE VIEW TC AS SELECT detail.id AS detail_id, detail_name AS TC, master.id AS master_id FROM master,detail WHERE detail.type_code=(SELECT code FROM type WHERE type.type = 'TC') AND detail.master_id=master.id;
SELECT * FROM TA;
SELECT * FROM TB;
SELECT * FROM TC;

SELECT master.master_name, TA.TA, TB.TB, TC.TC
FROM TA
FULL OUTER JOIN TB
ON ta.detail_id = tb.detail_id
FULL OUTER JOIN TC
ON ta.detail_id = tc.detail_id
JOIN master ON master.id = ta.master_id OR master.id = tb.master_id OR master.id = tc.master_id;


which gives:


master_name | ta | tb | tc
-------------+-------+-------+-------
M1 | M1,D1 | |
M1 | M1,D3 | |
M3 | M3,D1 | |
M3 | M3,D3 | |
M3 | M3,D4 | |
M3 | M3,D5 | |
M3 | M3,D6 | |
M3 | M3,D8 | |
M3 | M3,D7 | |
M1 | | M1,D2 |
M3 | | M3,D2 |
M2 | | | M2,D4
M2 | | |
M1 | | |
M2 | | | M2,D4
(15 rows)

joe
--
speigle
www.sirfsup.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org