Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    63

    Unanswered: Complex sql - shareable documents

    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...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select distinct
           d.ID
         , d.Description
      from employee as e1
    inner
      join employee as e2
        on e2.ParentCompanyID = e1.ParentCompanyID  
    inner
      join document
        on d.EmployeeID in (e1.EmployeeID
                           ,e2.EmployeeID)
       and d.SharingID = 1
     where e1.EmployeeID = $eid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2005
    Posts
    63
    Quote Originally Posted by r937
    try this --
    Code:
    select distinct
           d.ID
         , d.Description
      from employee as e1
    inner
      join employee as e2
        on e2.ParentCompanyID = e1.ParentCompanyID  
    inner
      join document
        on d.EmployeeID in (e1.EmployeeID
                           ,e2.EmployeeID)
       and d.SharingID = 1
     where e1.EmployeeID = $eid

    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...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    right, i overlooked the company
    Code:
    select distinct
           d.ID
         , d.Description
      from employee as e1
    inner
      join company as c1
        on c1.ID = e1.CompanyID
    inner
      join company as c2    
        on c2.ParentCompanyID = c1.ParentCompanyID  
    inner
      join employee as e2
        on e2.CompanyID = c2.ID
    inner
      join document
        on d.EmployeeID = e1.EmployeeID
        or (
           d.EmployeeID = e2.EmployeeID)
       and d.SharingID = 1
           )
     where e1.EmployeeID = $eid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •