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
