full join question... - Mailing list pgsql-general

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