From: paallen@attglobal.net
To: pgsql-sql@postgresql.org
Subject: Joint a table back on itself?
Date: Tue, 12 Sep 2006 15:55:58 +0000
Message-ID: <20060912160100.D624D9FA374@postgresql.org> (raw)
Hi all,
I have a union query that generates a table with
directional measurments (a=azimuth, i=depth) at
various depths (md) down a hole. The results look
like:
hole_id | md | a | i | e
| n | v
-----------+--------+--------+--------+------------------+------------------+------------------
GND-06-65 | 0 | 90 | -75 |
795187.927 | 9228405.685 | 3945.199
GND-06-65 | 19 | 90.37 | -74.42 |
795192.937315893 | 9228405.66852282 | 3926.87160812059
GND-06-65 | 28 | 91.18 | -74.49 |
795195.348994385 | 9228405.63593718 | 3918.20081588081
GND-06-65 | 37 | 91.04 | -74.53 |
795197.752173187 | 9228405.5893705 | 3909.52772202531
GND-06-65 | 46 | 92.38 | -74.56 |
795200.149282893 | 9228405.51783377 | 3900.85313364721
GND-06-65 | 55 | 92.86 | -74.55 |
795202.543576384 | 9228405.40826886 | 3892.17815120329
The depths ( md column) will always start with
zero and the intervals will be variable.
So how can I join this view back onto itself so
each record is joined to the next record? Such as:
md1 | a1 | i1 | e1 |
n1 | v1 | md2 | a2
| i2 | e2 | n2 |
v2 |
--------+--------+--------+------------------+------------------+------------------|--------+--------+--------+------------------+------------------+------------------
0 | 90 | -75 | 795187.927 |
9228405.685 | 3945.199 | 19 | 90.37
| -74.42 | 795192.937315893 | 9228405.66852282 |
3926.87160812059
19 | 90.37 | -74.42 | 795192.937315893 |
9228405.66852282 | 3926.87160812059 | 28 |
91.18 | -74.49 | 795195.348994385 |
9228405.63593718 | 3918.20081588081
28 | 91.18 | -74.49 | 795195.348994385 |
9228405.63593718 | 3918.20081588081 | 37 |
91.04 | -74.53 | 795197.752173187 |
9228405.5893705 | 3909.52772202531
37 | 91.04 | -74.53 | 795197.752173187 |
9228405.5893705 | 3909.52772202531 | 46 |
92.38 | -74.56 | 795200.149282893 |
9228405.51783377 | 3900.85313364721
46 | 92.38 | -74.56 | 795200.149282893 |
9228405.51783377 | 3900.85313364721 | 55 |
92.86 | -74.55 | 795202.543576384 |
9228405.40826886 | 3892.17815120329
My reason for wanting this is so I can joint this
table with a between clause to another table with
depth measurments recorded along this hole and
perform a calculation.
Thanks,
Phillip J. Allen
Consulting Geochemist
paallen@attglobal.net
The union query is as follows:
SELECT c.hole_id, 0 AS md, c.collar_azimuth AS a,
c.collar_dip AS i, c.e_utm AS e, c.n_utm AS n,
c.elv_utm AS v
FROM dh_collar AS c
WHERE (((c.hole_id)='GND-06-65'))
UNION
SELECT s.hole_id, s.depth_m AS md, s.azimuth AS a,
s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v
FROM dh_survey AS s
WHERE (((s.hole_id)='GND-06-65'))
UNION SELECT s.hole_id, c.depth_m AS md, s.azimuth
AS a, s.vert_dip AS i, s.XE AS e, s.XN AS n, s.XR AS v
FROM dh_collar AS c RIGHT JOIN dh_survey AS s ON
c.hole_id = s.hole_id
WHERE (((s.depth_m)=
(
SELECT Max(stmp.depth_m) AS MaxOfdepth_m
FROM dh_survey AS stmp
GROUP BY stmp.hole_id
HAVING (((stmp.hole_id)='GND-06-65'))
)) AND ((s.hole_id)='GND-06-65'));
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: paallen@attglobal.net
Subject: Re: Joint a table back on itself?
In-Reply-To: <20060912160100.D624D9FA374@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox