Thread: full join question...
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
On 5 apr 2006, at 16.39, Jonas F. Henriksen wrote: > 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. > All help apreciated (hope you understand what I want to do...), Not sure I understand why you need a join... You say you want to retrieve all depths that have certain measuretypes. Wouldn't the following query do? SELECT DISTINCT depth FROM testtable WHERE measuretype IN (1040, 4001); Is there anything else you need from the query as well? Mvh, Niklas Johansson Tel: 0322-108 18 Mobil: 0708-55 86 90
Hi,
ehh, sorry, yes, I also want to retrieve other values from the table,
but I left them out for clarity (which made it maby less clear...).
Maybe it makes more sense if you define the table as
CREATE TABLE testtable (
depth integer,
measuretype integer,
operation integer,
value float
);
...where I'm actually interested in the value...
Jonas:))
On Wed, 2006-04-05 at 17:26 +0200, Niklas Johansson wrote:
> On 5 apr 2006, at 16.39, Jonas F. Henriksen wrote:
>
> > 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.
>
>
> > All help apreciated (hope you understand what I want to do...),
>
>
> Not sure I understand why you need a join... You say you want to
> retrieve all depths that have certain measuretypes. Wouldn't the
> following query do?
>
> SELECT DISTINCT depth FROM testtable WHERE measuretype IN (1040, 4001);
>
> Is there anything else you need from the query as well?
>
>
> Mvh,
>
> Niklas Johansson
> Tel: 0322-108 18
> Mobil: 0708-55 86 90
>
>
>
Well, the problem is I want the result on one row for each depth, so it will fit nicely into a table, like this: depth measuretype1_value measuretype2_value 10 1.78 2.55 20 2.12 30 3.12 40 1.3 1.4 ...with missing rows for a depth appearing as missing values... Jonas:)) On Wed, 2006-04-05 at 20:06 +0200, Niklas Johansson wrote: > On 5 apr 2006, at 18.37, Jonas F. Henriksen wrote: > > ehh, sorry, yes, I also want to retrieve other values from the table, > > but I left them out for clarity (which made it maby less clear...). > > Maybe it makes more sense if you define the table as > > CREATE TABLE testtable ( > > depth integer, > > measuretype integer, > > operation integer, > > value float > > ); > > ...where I'm actually interested in the value... > > > Well, is there something else I don't get or couldn't you just select > that as well (perhaps without the DISTINCT then)? > > SELECT depth, value FROM testtable WHERE measuretype IN (1040, 4001); > > > > Mvh, > > Niklas Johansson > Tel: 0322-108 18 > Mobil: 0708-55 86 90 > > >
On 5 apr 2006, at 18.37, Jonas F. Henriksen wrote: > ehh, sorry, yes, I also want to retrieve other values from the table, > but I left them out for clarity (which made it maby less clear...). > Maybe it makes more sense if you define the table as > CREATE TABLE testtable ( > depth integer, > measuretype integer, > operation integer, > value float > ); > ...where I'm actually interested in the value... Well, is there something else I don't get or couldn't you just select that as well (perhaps without the DISTINCT then)? SELECT depth, value FROM testtable WHERE measuretype IN (1040, 4001); Mvh, Niklas Johansson Tel: 0322-108 18 Mobil: 0708-55 86 90
"Jonas F. Henriksen" <jonas.f.henriksen@imr.no> writes:
> Well, the problem is I want the result on one row for each depth, so it
> will fit nicely into a table, like this:
> depth measuretype1_value measuretype2_value
> 10 1.78 2.55
> 20 2.12
> 30 3.12
> 40 1.3 1.4
I think what you have to do is filter the rows according to measuretype
*before* you join them. Something like
select * from
(select depth, value as measuretype1_value from testtable
where measuretype = 1040) ss1
full join
(select depth, value as measuretype2_value from testtable
where measuretype = 4001) ss2
using (depth);
regards, tom lane
Thanks Tom, that worked great!! Regards Jonas:)) On Wed, 2006-04-05 at 15:09 -0400, Tom Lane wrote: > "Jonas F. Henriksen" <jonas.f.henriksen@imr.no> writes: > > Well, the problem is I want the result on one row for each depth, so it > > will fit nicely into a table, like this: > > > depth measuretype1_value measuretype2_value > > 10 1.78 2.55 > > 20 2.12 > > 30 3.12 > > 40 1.3 1.4 > > I think what you have to do is filter the rows according to measuretype > *before* you join them. Something like > > select * from > (select depth, value as measuretype1_value from testtable > where measuretype = 1040) ss1 > full join > (select depth, value as measuretype2_value from testtable > where measuretype = 4001) ss2 > using (depth); > > regards, tom lane
On 5 apr 2006, at 18.37, Jonas F. Henriksen wrote: > ehh, sorry, yes, I also want to retrieve other values from the table, > but I left them out for clarity (which made it maby less clear...). > Maybe it makes more sense if you define the table as > CREATE TABLE testtable ( > depth integer, > measuretype integer, > operation integer, > value float > ); > ...where I'm actually interested in the value... Well, is there something else I don't get or couldn't you just select that as well (perhaps without the DISTINCT then)? SELECT depth, value FROM testtable WHERE measuretype IN (1040, 4001); Sincerely, Niklas Johansson