Nesting subselects in one statement - Mailing list pgsql-general

Hi Peoples,
   While I've been using PostgreSQL for about six months now, and
databases in general for a bit longer than that, I still only have a
grasp on what I would consider simple SQL statements. As such I want to
know if I can do something, but after reading the SELECT statement
def's, it sounds like I can, but I'm not entirely sure if I can.

To see the design, read on. But Essentially what I want in a single
select statement is:
   Given username  x, return that users accounts and roles, and then for
all the accounts for user x, return all the other usernames and roles
associated with those accounts.

Is that possible??

Anyway, I've included the table defs and view defs below.

Okay, I've got a number of tables.
  account
  account_contact
  account_role
  account_contact_account_contact_role
  contact

account holds information on an account. Has a unique identifier
(BigInt) called id and a name field.
account.id
account.name

contact is info about a user of the system. Again has a unique
identifier (BigInt) called id and a username field.
contact.id
contact.username

account_contact is a table that joins an account and contact together
but rather than using a composite key, also has a unique id field.
account and contact are foreign key relationships to account and contact
respectively.
  account_contact.id
  account_contact.account >> account.id
  account_contact.contact >> contact.id

account_role are roles that an performed by contacts against accounts.
  account_role.id
  account_role.name

Finally, account_contact_account_contact_role is a composite key to
account_contact and account_contact_role. Essentially, this
account_contact performs this role.

  account_contact_account_contact_role.account_contact >>
account_contact.id
  account_contact_account_contact_role.account_contact_role >>
account_contact_role.id


SO.... If you've stuck with me to now well done ;-)

I then created a view as such.

create view v_acc_acc_ctct_acc_ctct_role AS
 select acc.account,
    a.name,
        acacr.account_contact,
        acc.contact,
        c.username,
    acacr.account_contact_role,
        acr.name
   from account_contact_acc_contact_role AS acacr
   LEFT JOIN account_contact AS acc ON acc.id = acacr.account_contact
   LEFT JOIN account AS a ON a.id = acc.account
   LEFT JOIN contact AS c ON c.id = acc.contact
   LEFT JOIN account_contact_role AS acr ON acr.id =
acacr.account_contact_role;

This yields the dataset I want, but I'm just not sure I can get what I
want in the one select from the view??

I'd appreciate any ideas or suggestions.

Thank You.
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.