Hi all. I'm trying to make an efficient query to list all documents related
to a company and also documents related to employees and projects for that
company. I have this sample-schema: create table entity( id integer primary
key, entity_type varchar not null, check (entity_type IN ('COMPANY', 'PERSON',
'PROJECT')) ); create table company( id integer primary key references
entity(id), name varchar not null ); create table person( id integer primary
key referencesentity(id), name varchar not null, company_id integer references
company(id) ); create table project( id integer primary key references
entity(id), name varchar not null, company_id integer references company(id) );
create table document( id integer primary key, name varchar not null ); create
tabledocument_usage( document_id integer not null references document(id),
entity_idinteger not null references entity(id) ); insert into entity(id,
entity_type)values(1, 'COMPANY'); insert into company(id, name) values(1, 'ACME'
); insert into entity(id, entity_type) values(2, 'PERSON'); insert into
person(id,name, company_id) values(2, 'Bill', 1); insert into entity(id,
entity_type)values(3, 'PROJECT'); insert into project(id, name, company_id)
values(3, 'Development', 1); insert into document(id, name) values(1, 'Doc 1');
insert into document(id, name) values(2, 'Doc 2'); insert into document(id, name
)values(3, 'Doc 3'); insert into document_usage(document_id, entity_id) values(1
,1); insert into document_usage(document_id, entity_id) values(1, 3); insert
intodocument_usage(document_id, entity_id) values(2, 2); insert into
document_usage(document_id, entity_id)values(3, 3); So, documents are related
to companies, persons or projects thru the document_usage table. I have this
query to list all documents for a specific company and related employees and
projects (belonging to that company) select doc.id, doc.name as document_name,
comp.nameas company_name, null as person_name, null as project_name from
documentdoc JOIN document_usage du ON doc.id = du.document_id JOIN company comp
ONdu.entity_id = comp.id WHERE comp.id = 1 UNION SELECT doc.id, doc.name as
document_name, comp.nameas company_name, pers.name as person_name, null as
project_name from document doc JOIN document_usage du ON doc.id = du.document_id
JOINperson pers ON pers.id = du.entity_id JOIN company comp ON comp.id =
pers.company_id WHERE comp.id = 1 UNION SELECT doc.id, doc.name as
document_name, comp.nameas company_name, null as person_name, proj.name as
project_name from document doc JOIN document_usage du ON doc.id = du.document_id
JOINproject proj ON proj.id = du.entity_id JOIN company comp ON comp.id =
proj.company_id WHERE comp.id = 1 order by document_name ; id | document_name
| company_name | person_name | project_name
----+---------------+--------------+-------------+--------------
1 | Doc 1 | ACME | |
1 | Doc 1 | ACME | | Development
2 | Doc 2 | ACME | Bill |
3 | Doc 3 | ACME | | Development
(4 rows) I'm looking for a more efficient query where I don't have to
repeat JOINing with document, document_usage and company all the time, and
somehow avoid the UNIONs. Anyone has a better solution respecting the schema?
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