Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535

    Help with a particular troublesome query


    I am totally stumped! I have the schema described below.

    Given persons.id and documents.id, I wish to return documents.filename (or NULL if none) if persons is associated with allProjects as dictated by associatedPeople

    I am thinking of a query similar the one below, but just don't have it right.

    Thank you!

    Code:
    Schema
    
    allProjects
    -id (PK)
    -data
    
    mainProjects
    -id (PK and also FK referencing allProjects.id)
    -data
    
    subProjects
    -id (PK and also FK referencing allProjects.id)
    -mainProjectsID (FK referencing mainProjectsID.id)
    -data
    
    associatedPeople
    -personsID (FK referencing persons.id)
    -allProjectsID (FK referencing allProjects.id)
    -data
    
    documents
    -id (PK)
    -allProjectsID (FK referencing allProjects.id)
    -filename
    
    persons
    -id (PK)
    -data
    Possible query?

    Code:
    SELECT d.filename
    FROM documents AS d
    INNER JOIN associatedPeople AS ap ON ap.allProjectsID=d.allProjectsID
    LEFT OUTER JOIN subProjects AS sp ON sp.id=d.allProjectsID
    LEFT OUTER JOIN (
        SELECT ap.peoplesID, mp.id
        FROM mainProjects AS mp
        INNER JOIN subProjects AS sp ON sp.mainProjectsID=mp.id
        INNER JOIN associatedPeople AS ap ON ap.allProjectsID=sp.id
    ) AS mp ON mp.id=d.allProjectsID
    WHERE d.id=123 AND (ap.peoplesID=321 OR mp.peoplesID=321)
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    The query you have looks more complicated if you only want document filename.
    Having the person.id, I would start with the table 'associatedPeople' and then get the person projects and possible related documents, something like this:
    Code:
    SELECT d.filename
    FROM associatedPeople AS ap 
    LEFT JOIN documents AS d ON d.allProjectsID=ap.allProjectsID
    WHERE ap.personsID = 321
    For specific document id, the WHERE clause should be extended
    with this information:
    Code:
    SELECT d.filename
    FROM associatedPeople AS ap 
    LEFT JOIN documents AS d ON d.allProjectsID=ap.allProjectsID
    WHERE ap.personsID = 321 AND d.id = 123
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by MrFujin
    Code:
    LEFT JOIN documents AS d ON d.allProjectsID=ap.allProjectsID
    WHERE ap.personsID = 321 AND d.id = 123
    by requiring a value for a column from documents table, you might as well write INNER JOIN because LEFT OUTER JOIN won't return any unmatched rows

    Comments on this post

    • MrFujin agrees : :)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    Thank you Gentlemen,

    I am sure I did not completely describe the requirements, and this query will unfortunately only return half the possible results.

    If a person is associated with a subProject, I also wish to include documents which are associated with the affiliated mainProject.

    Note that allProjects is a super table for both mainProjects and subProjects. Furthermore, subProjects includes a FK which references mainProjects. Thus a document can be tied to a main project which includes several sub projects in which the person is associated with.

    I've been going round and round so long, my head is starting to hurt
  8. #5
  9. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    Let see if I understand it correct
    1. Specify a person Id and document Id.
    2. Check that the person and document id are related to the same "all Project" id (APID)
    3. get all document related to this APID
    4. If APID is a subproject, get all document from the main project
    5. get all documents from sub_projects related to the main project

    This is my comments/view on your current query:
    * Use LEFT JOIN instead of INNER JOIN to join sub project to the main table in the nested selected join.
    * Keep alias unique, at least to prevent misunderstanding.
    * Think associatedPeople in nested join should be linked/joined to the main project.
    * Missing files connection to subprojects linked to main project
    * maybe add distinct to only get unique filename (you are joining sub_project twice)
    * Not sure whether "WHERE d.id=123" will limit the result too much

    sql Code:
     
    SELECT DISTINCT d.filename
    FROM documents AS d
    INNER JOIN associatedPeople AS ap ON ap.allProjectsID=d.allProjectsID
    LEFT OUTER JOIN subProjects AS sp ON sp.id=d.allProjectsID
    LEFT OUTER JOIN (
        SELECT ap.peoplesID AS personID, mp.id AS MPID, sp2.id AS SPID 
        FROM mainProjects AS mp
        INNER JOIN associatedPeople AS ap2 ON ap2.allProjectsID=mp.id
        LEFT JOIN subProjects AS sp2 ON sp2.mainProjectsID=mp.id
    ) AS msp ON (msp.MPID = d.allProjectsID OR msp.SPID = d.allProjectsID)
    WHERE d.id=123 AND (ap.peoplesID=321 OR msp.personID=321)


    Don't know if it will make any sense to split it into separated statements and then use UNION (ALL) to combine the result.
    Last edited by MrFujin; February 19th, 2013 at 07:18 PM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    Thanks MrFujin,

    Looks like you understand correctly. Maybe a better way to describe it would be to ensure that all documents belong to a mainProject or subProject where a person also belongs to that same mainProject or subProject.

    The part that has me scratching my head is I do not start off with a given mainProject or subProject ID.

    The UNION idea would be something like the following. Just seems like I shouldn't be using a union. I think you have something going on with your suggestion about DISTINCT.
    Code:
    SELECT d.id
    FROM documents AS d
    INNER JOIN associatedPeople AS ap ON ap.allProjectsID=d.allProjectsID
    WHERE d.id=123 AND ap.peopleID=321
    UNION
    SELECT DISTINCT d.id
    FROM documents AS d
    INNER JOIN mainProjects AS pm ON mp.id=d.mp.allProjectsID
    INNER JOIN subProjects AS sp ON sp.mainProjectsID=mp.id
    INNER JOIN associatedPeople AS ap ON ap.allProjectsID=sp.id
    WHERE d.id=123 AND ap.peopleID=321
    I liked the nice cute simple query more! Seems like I am going about this dreadfully wrong.
  12. #7
  13. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    We have several queries, but you haven't really told what is missing.
    How does the result differ from you expectation?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    MrFujin, Thank you for your persistence. I ended up getting it working without using a UNION or DISTINCT. It was the super/sub tables that just had me confused.
  16. #9
  17. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    Great to hear you got it working, can you tell/post how you setup the final query?

    UNION and DISTINCT was also just some thoughts, definitely not mandatory.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    can you tell/post how you setup the final query?
    Just the two outer joins that I originally showed. But instead of:
    Code:
    AS mp ON mp.id=d.allProjectsID
    I used
    Code:
    AS sp ON mp.id=d.allProjectsID
    Thanks again for your help
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by NotionCommotion
    I used
    Code:
    AS sp ON mp.id=d.allProjectsID
    if you think about this, it doesn't make any sense

    you are joining the sp table to the joined tables, but you are specifying a join condition which references two other tables and does not reference sp at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    One thing more, how does SQL react to duplicated alias? sp is used twice.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by MrFujin
    One thing more, how does SQL react to duplicated alias? sp is used twice.
    1. test it and see
    or
    2. post it and i'll check it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    if you think about this, it doesn't make any sense
    Agree. Make that the following. What do you think of my use of using "ap.personsID=321" in the ON clauses for associatedPeople JOIN, instead of the WHERE clause?
    Code:
    SELECT d.filename
    INNER JOIN documents AS d
    LEFT OUTER JOIN (
    SELECT apr.id
    FROM allProjects AS apr
    INNER JOIN subProjects AS sp ON sp.id=apr.id
    INNER JOIN associatedPeople AS ap ON ap.allProjectsID=sp.id AND ap.personsID=321
    ) AS s ON s.id=d.allProjectsID
    LEFT OUTER JOIN (
    SELECT apr.id
    FROM allProjects AS apr
    INNER JOIN mainProjects AS mp ON mp.id=apr.id
    INNER JOIN subProjects AS sp ON sp.mainProjectsID=mp.id
    INNER JOIN associatedPeople AS ap ON ap.allProjectsID=sp.id AND ap.personsID=321
    ) AS c ON c.id=d.allProjectsID
    WHERE d.id=123
    One thing more, how does SQL react to duplicated alias? sp is used twice.
    Subqueries react like global/local scope.
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by NotionCommotion
    What do you think of my use of using "ap.personsID=321" in the ON clauses for associatedPeople JOIN, instead of the WHERE clause?
    i don't like it

    if there's a condition that filters rows, then i would write the associated table as the first table in the FROM clause

    you start with that table, and the WHERE clause applies the filter, thus only one row is returned, and from that point on, only matching rows are joined

    this is better than starting with some table, doing some joins, and then throwing most of the joined rows away when you get to the filtered table

    now, optimizers are usually smart enough to take this second type of FROM clause and figure out which table to start with, but i prefer to write the FROM clause the "optimized" way because the intent of what you're after is a lot clearer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo