Re: Querying all documents for a company and its projects etc

what about resolving the join "document doc JOIN document_usage du ON
doc.id = du.document_id" using WITH? I think this won't work because
the result of this CTE would be huge,
but you could get the company with that id first using a WITH clause
too, then you would have the name and you wouldn't have to join with
the company table.

2014-04-16 5:11 GMT-03:00 Andreas Joseph Krogh <andreak(at)officenet(dot)no>:
> På mandag 14. april 2014 kl. 21:55:26, skrev David G Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com>:
>
> 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(at)officenet(dot)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
>