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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
