Company => ID, ParentCompanyID
Employee => ID, Company.ID
Document => ID, Employee.ID, Sharing.ID
Sharing => ID, Description (data: 0 - Private, 1 - Public)

I am trying to build a dropdown of shareable documents created by employees and other employees that belong to the same company (same branch and other branch offices) who set the sharing flag for the documents to Public:

select ID, Description
from document
where EmployeeID = $eid
or EmployeeID in
( select e.ID
from employee e, document d
where e.ID = d.EmployeeID
and e.ID != $eid -- should not re-include current employee's documents
and d.SharingID = 1
and e.CompanyID =
( select CompanyID -- same branch
from employee e2, company c
where e2.ID = $eid
and c.ID = e2.CompanyID
or c.ParentCompanyID =
( select ParentCompanyID -- other branches
from company
where ... -- ?
and )
)

It got too complicated and confusing. I would like to use some EXISTS clause to improve performance and break the statement into multiple statements if required:

// Current employee's documents
select ID, Description
from document
where EmployeeID = $eid


// Employees of the same branch
select e.ID
from employee e, document d
where e.ID = d.EmployeeID
and e.ID != $eid -- should not re-include current employee's documents
and d.SharingID = 1
and e.CompanyID =
( select CompanyID -- same branch
from employee e2, company c
where e2.ID = $eid
and c.ID = e2.CompanyID )


// Employees of other branches
...

Please help...

try this --

ParentCompanyID column resides in the company table. This is a unique HeadOffice (single-level hierarchical data) for all the branches for simplicity. Company table is linked to the employee table but the above statement does not refer to the company table:

Company => ID, ParentCompanyID
Employee => ID, CompanyID
Document => ID, EmployeeID, SharingID
Sharing => ID, Description (data: 0 - Private, 1 - Public)

Here are the requirements:

Include all the documents of the current employee ($eid) - public/private.

Include only the shareable (public) documents of the employees of the same branch of $eid.

Include only the shareable (public) documents of the employees of the other branches of the company.

Thanks for your time...

right, i overlooked the company