SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Mar 2002
    Location
    Miami, Florida - Caracas, Venezuela
    Posts
    379
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Please help me with this query.

    I'm building a simple system that allows someone in my client's onganization to post files for certain users to download. In our case the best way for the administrator of the system to manage access to files is to have users, groups, documents and overrides. The point being that you create groups, add documents to groups, then add users to groups, then add or remove documents on a user by user basis.

    So here are my tables:

    users
    =============
    = id
    = alias
    = password

    groups
    =============
    = id
    = name

    documents
    =============
    = id
    = name
    = data
    = ...

    groups_users
    =============
    = group_id
    = user_id

    documents_groups
    =============
    = document_id
    = group_id

    documents_users
    =============
    = document_id
    = user_id
    = switch


    PROBLEM
    =============
    I need to create a query that returns all documents a user has access to based on what group the user belongs to and taking into account that the document does NOT appear related to the user in documents_users with switch set to 0. Now, after getting that, add the files that DO appear related to the user in documents_users with switch set to 1.

    Basically get documents for user based on group and add or remove documents to the users results based on documents_users.switch.

    Please, please, please help!!! I'm loosing too much sleep over this!!!

    Thank you
    Luis

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select d.id
         , d.name
         , d.data
      from users as u
    inner
      join groups_users as gu
        on u.id = gu.user_id
    inner
      join documents_groups as dg
        on gu.group_id = dg.group_id   
    inner
      join documents as d
        on dg.document_id = d.id    
    left outer
      join documents_users as du
        on u.id = du.user_id   
       and du.switch = 0
     where u.id = 937
       and du.user_id is null
    union all
    select d.id
         , d.name
         , d.data
      from users as u   
    inner
      join documents_users as du
        on u.id = du.user_id   
       and du.switch = 1      
    inner
      join documents as d
        on du.document_id = d.id 
     where u.id = 937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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