Extract interdependent info from one table

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         |
charactervarying(25)  |subclass_alias | character varying(25)  |docnum         | integer                | not
nullversion       | 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