From: Andrej Ricnik-Bay <andrej.groups@gmail.com>
To: sql pgsql <pgsql-sql@postgresql.org>
Subject: Extract interdependent info from one table
Date: Fri, 25 Jan 2008 16:32:51 +1300
Message-ID: <b35603930801241932j30499965i3d75229beb24e13e@mail.gmail.com> (raw)
Hi Guys,
I can't quite wrap my head around this one ...
I have
\d docmaster
Table "public.docmaster"
Column | Type | Modifiers
----------------+------------------------+-----------
alias1 | integer |
alias2 | character varying(25) |
subclass_alias | character varying(25) |
docnum | integer | not null
version | integer |
docname | character varying(255) |
Indexes:
"docmaster_docnum_key" UNIQUE, btree (docnum)
with the following data:
# select * from docmaster ;
alias1 | alias2 | subclass_alias | docnum | version | docname
--------+-----------+----------------+--------+---------+-----------------
3589 | Completed | Inquiry | 653218 | 1 | My greater doc2
3587 | Pending | Post | 653216 | 3 | My great doc1
3588 | Draft | Reply | 653217 | 1 | My great doc2
3587 | Draft | Reply | 653219 | 2 | My greater doc4
(4 rows)
Now I want to find inquiries (subclass_alias = 'Inquiry'), list their status and
also (if there's another row that a) has the same alias1, a subclass
of Reply and a status (alias2) of pending or redraft.... how do I
achieve this?
What I have is
select docnum, alias1, alias2, subclass_alias from docmaster where
(alias1 = ( select alias1 from docmaster where subclass_alias = 'Post'
and ( alias2 = 'Pending' or alias2 = 'Redraft' ))) and ( alias2 =
'Pending' or alias2 = 'Draft' ) and subclass_alias <> 'Post' ;
docnum | alias1 | alias2 | subclass_alias
--------+--------+---------+----------------
653219 | 3587 | Redraft | Reply
(1 row)
What I'd really like is to BOTH Post AND reply, with the alias2 for both.
Hope this was as clear as mud? :)
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
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: andrej.groups@gmail.com
Subject: Re: Extract interdependent info from one table
In-Reply-To: <b35603930801241932j30499965i3d75229beb24e13e@mail.gmail.com>
* 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