Nesting subselects in one statement
От | Hadley Willan |
---|---|
Тема | Nesting subselects in one statement |
Дата | |
Msg-id | 1048803645.1773.28.camel@atlas.sol.deeper.co.nz обсуждение исходный текст |
Список | 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.
В списке pgsql-general по дате отправления: