From: | "Andrej Ricnik-Bay" <andrej(dot)groups(at)gmail(dot)com> |
---|---|
To: | "sql pgsql" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Extract interdependent info from one table |
Date: | 2008-01-25 03:32:51 |
Message-ID: | b35603930801241932j30499965i3d75229beb24e13e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Phillip Smith | 2008-01-25 04:11:30 | Re: Extract interdependent info from one table |
Previous Message | Scott Marlowe | 2008-01-24 20:59:52 | Re: date format |