Skip site navigation (1) Skip section navigation (2)

Extract interdependent info from one table

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 (view raw or flat)
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.

http://www.american.edu/econ/notes/htmlmail.htm

Responses

pgsql-sql by date

Next:From: Phillip SmithDate: 2008-01-25 04:11:30
Subject: Re: Extract interdependent info from one table
Previous:From: Scott MarloweDate: 2008-01-24 20:59:52
Subject: Re: date format

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group