|
Home > Archive > PostgreSQL Discussion > April 2006 > full join question...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
full join question...
|
|
| Jonas F. Henriksen 2006-04-05, 11:31 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Niklas Johansson 2006-04-05, 11:31 am |
|
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Jonas F. Henriksen 2006-04-05, 1:37 pm |
| 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:
>
>
>
>
>
> 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
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Jonas F. Henriksen 2006-04-05, 8:25 pm |
| 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:
>
>
> 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
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Niklas Johansson 2006-04-05, 8:25 pm |
|
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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Tom Lane 2006-04-05, 8:25 pm |
| "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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Jonas F. Henriksen 2006-04-06, 3:26 am |
| 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:
>
>
> 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
|
|
|
|