Hi,
I'm working with postgres and I have a question regarding a self-join on
my table (se below for table definition and testdata):
I want to retrieve all depths that have either a measuretype=1040 or a
measuretype=4001 or both. I've tried:
select * from
testtable t1
full outer join testtable t2
on( t1.operation=t2.operation and t1.depth=t2.depth
and t1.measuretype=1040 and t2.measuretype=4001)
where t1.operation=824419
This however does not restrict t1.measuretype to only 1040 but retrieves
all values for t1.
Have also tried using t1.measuretype=1040 in the where-condition:
select * from
testtable t1
full outer join testtable t2
on( t1.operation=t2.operation and t1.depth=t2.depth
and t1.measuretype=1040 and t2.measuretype=4001)
where t1.operation=824419
and t1.measuretype=1040
depth | measuretype | operation | depth | measuretype | operation
-------+-------------+-----------+-------+-------------+-----------
100 | 1040 | 824419 | | |
74 | 1040 | 824419 | 74 | 4001 | 824419
49 | 1040 | 824419 | 49 | 4001 | 824419
29 | 1040 | 824419 | | |
19 | 1040 | 824419 | | |
9 | 1040 | 824419 | 9 | 4001 | 824419
4 | 1040 | 824419 | 4 | 4001 | 824419
...which gives the result I want (jippiiii?), but would return to few
rows if t1.measuretype=1040 only was found on some depths, like if you
switch t1 and t2:
select * from
testtable t1
full outer join testtable t2
on( t1.operation=t2.operation and t1.depth=t2.depth
and t1.measuretype=4001 and t2.measuretype=1040)
where t1.operation=824419
and t1.measuretype=4001
depth | measuretype | operation | depth | measuretype | operation
-------+-------------+-----------+-------+-------------+-----------
74 | 4001 | 824419 | 74 | 1040 | 824419
49 | 4001 | 824419 | 49 | 1040 | 824419
9 | 4001 | 824419 | 9 | 1040 | 824419
4 | 4001 | 824419 | 4 | 1040 | 824419
Anyone know how I can make this query so it returns all rows for all
measuretypes, regardless of which is joining which?
All help apreciated (hope you understand what I want to do...),
regards Jonas:)))
Testdata and testtable definition:
CREATE TABLE testtable (
depth integer,
measuretype integer,
operation integer
);
INSERT INTO testtable VALUES (100, 1100, 824419);
INSERT INTO testtable VALUES (100, 1080, 824419);
INSERT INTO testtable VALUES (100, 1060, 824419);
INSERT INTO testtable VALUES (100, 1040, 824419);
INSERT INTO testtable VALUES (74, 4002, 824419);
INSERT INTO testtable VALUES (74, 4001, 824419);
INSERT INTO testtable VALUES (74, 1100, 824419);
INSERT INTO testtable VALUES (74, 1080, 824419);
INSERT INTO testtable VALUES (74, 1060, 824419);
INSERT INTO testtable VALUES (74, 1040, 824419);
INSERT INTO testtable VALUES (49, 4002, 824419);
INSERT INTO testtable VALUES (49, 4001, 824419);
INSERT INTO testtable VALUES (49, 1100, 824419);
INSERT INTO testtable VALUES (49, 1080, 824419);
INSERT INTO testtable VALUES (49, 1060, 824419);
INSERT INTO testtable VALUES (49, 1040, 824419);
INSERT INTO testtable VALUES (29, 1100, 824419);
INSERT INTO testtable VALUES (29, 1080, 824419);
INSERT INTO testtable VALUES (29, 1060, 824419);
INSERT INTO testtable VALUES (29, 1040, 824419);
INSERT INTO testtable VALUES (19, 1100, 824419);
INSERT INTO testtable VALUES (19, 1080, 824419);
INSERT INTO testtable VALUES (19, 1060, 824419);
INSERT INTO testtable VALUES (19, 1040, 824419);
INSERT INTO testtable VALUES (9, 4002, 824419);
INSERT INTO testtable VALUES (9, 4001, 824419);
INSERT INTO testtable VALUES (9, 1100, 824419);
INSERT INTO testtable VALUES (9, 1080, 824419);
INSERT INTO testtable VALUES (9, 1060, 824419);
INSERT INTO testtable VALUES (9, 1040, 824419);
INSERT INTO testtable VALUES (4, 4002, 824419);
INSERT INTO testtable VALUES (4, 4001, 824419);
INSERT INTO testtable VALUES (4, 1100, 824419);
INSERT INTO testtable VALUES (4, 1080, 824419);
INSERT INTO testtable VALUES (4, 1060, 824419);
INSERT INTO testtable VALUES (4, 1040, 824419);
--
Jonas F Henriksen
Institute of Marine Research
Norsk Marint Datasenter
PO Box 1870 Nordnes
5817 Bergen
Norway
Phone: +47 55238441
