Re: Joining a result set from four (4) tables
От | John Tregea |
---|---|
Тема | Re: Joining a result set from four (4) tables |
Дата | |
Msg-id | 44D02242.5050309@debraneys.com обсуждение исходный текст |
Ответ на | Re: Joining a result set from four (4) tables ("Aaron Bono" <postgresql@aranya.com>) |
Ответы |
Re: Joining a result set from four (4) tables
("Aaron Bono" <postgresql@aranya.com>)
|
Список | pgsql-sql |
Hi Aaron, Thanks very much, I really appreciate both the solution and the advice about naming conventions. Your script worked fine and gives me an example of what to do for other situations as well. Because the logic structure of this software is in the front end application rather than the database there is a strong need to keep the naming of fields generic rather than specific, I am not using pre-defined foreign keys at all. If I was building the database with a purpose specific goal I would be doing what you say. I have found though that when I label elements at different levels of the back end for one purpose, they are less transportable in the long run. In this case the naming conventions are actually stored in another table and applied as aliases when needed. That way I can change the names and labels (for a new client or industry) while the underlying structure remains the same. I hope to increase interoperability in this way as well. Thanks again for your help. Kind regards John T. Aaron Bono wrote: > > First of all, I must say it is very unfortunate that all the tables > have the same name for their primary key column. It made things > confusing at first when reading through the tables (the foreign key > names don't match the primary keys they map to). It also makes it > more likely you will do a join improperly. > > Worse, your foriegn key names are very ambiguous. The name related_id > says nothing about what table it maps to. That means you need > documentation or the foreign key definitions (are you using foreign > key constraints?) to tell what is going on. > > Anyway... on to solving your problem. > > SELECT > permission.serial_id, > resource.name <http://resource.name/>, > actions.name <http://actions.name/>, > actions.classification, > actions.display_group > FROM permission > INNER JOIN policies ON ( > policies.serial_id = permission.related_id > ) > INNER JOIN actions ON ( > actions.serial_id = policies.related_id > ) > INNER JOIN resource ON ( > -- This is tricky as policies maps to resources AND actions maps to > resources > -- so the real question is which one do you do? I did both. > policies.resource_id = resources.serial_id > AND > actions.related_id = resources.serial_id > ) > WHERE > permission.user_id = '11' AND > policies.status = 'Active' AND > permission.status = 'Active'AND > actions.status = 'Active'AND > resource.status = 'Active' > ; > > I always discourage listing more than one table in the FROM clause. > Use INNER and OUTER JOINs - it is much easier to debug and it is > somewhat self documenting. That way, when you or another developer > look at this in the future, you understand right away how the tables > are being put together. > > ================================================================== > Aaron Bono > Aranya Software Technologies, Inc. > http://www.aranya.com > ==================================================================
В списке pgsql-sql по дате отправления:
Предыдущее
От: "Rodrigo De Leon"Дата:
Сообщение: Re: viewing the description of tables from python DB-API