A couple of thoughts:
1) The "union" really only pertains to the entity table rows; once you
"union all" those (duplicates should not matter and probably will not even
be present so using "all" avoids an unnecessary sort) you can join that
sub-query to the document_usage table.
2) Since every entity must have an associated company moving the company_id
field to the entity table will allow a direct search for company objects
using entity alone and remove the need to perform the union. The "company"
table omits the implicit self-referencing company_id but it is still there
in reality.
David J.
Thanks for having a look at it.
I'm afraid I don't understand what you mean in 1); Can you give an example of the query you're suggesting, joining "that subquery"?
2) Note that I need the names of the company owning each entity(person, project etc.), and the details about those entities (name and ID), and in my real app there are more entities which are not directly related to company, but indirectly thru a project (ie. a task) and I want to be able to list those entities' documents too.
Do you have a suggestion for a better $subject for this kind of problem?
thanks.
--
Andreas Joseph Krogh <andreak@officenet.no> mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc