Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: embedded select, or join, with max()??

    That's probably not a useful title, sorry.

    This is what I have:

    Code:
    SELECT
    p.id AS projectID,
    p.project_name,
    MAX(f.upload_date) AS LastFileUpload,
    MAX(cm.comment_date) AS LastFileComment,
    
    FROM studioProjects p
    
    INNER JOIN studioCompaniesProjects cp ON p.id = cp.project_id
    INNER JOIN studioCompanies c ON cp.company_id = c.id
    INNER JOIN studioUsers u ON u.company_id = cp.company_id AND u.id=19
    
    LEFT JOIN studioFiles f ON f.project_id = p.id
    LEFT JOIN studioFileComments cm ON cm.file_id = f.id
    
    GROUP BY p.id
    It works - it'll give me a list of assigned projects, with the last activity dates, for a particular user ID.

    But I also want to pull out the id and name of the person associated with that activity - who made the last comment on a file, and who uploaded the last file.

    studioFiles has column user_id
    studioFileComments has column comment_by_id
    studioUsers has column username

    I can't figure out if this should be some sort of embedded select, or if I can continue pasting JOINs in there, and my brain is starting to wilt.

    Hopefully I've given enough info, I can put all the relevant CREATE TABLE sql up if necessary.

    Thanks for any advice.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    embedded selects is the way to go

    can you please give me the one-to-many relationships involved?

    e.g. each p has multiple cp's, each cp has only one c, each c has multiple u's but only one u 19

    etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    I'll try!

    each project has multiple companies and each company has multiple projects (p is linked to c via cp)

    each company has multiple users, but a user is only associated with one company (and yes there is only one user with ID 19 - the 'id' field is the pk for any given table)

    each project has multiple files (but a file is only associated with one project)
    each user has multiple files (but a file is only associated with one user)

    a user can have multiple comments, and a file can have multiple comments, but a comment is associated with only one user and one file.

    Aaagh. That sounds confusing to me, and I built the thing. Hopefully it's some help though...

  4. #4
    Join Date
    Jan 2002
    Posts
    189
    Ok, I've been poking/experimenting.

    This is what I have now:

    Code:
    SELECT
    p.id AS projectID,
    p.project_name,
    fcm.comment AS lastComment,
    fcm.username AS lastCommentBy,
    fcm.comment_date AS lastCommentDate,
    fcm.title AS lastCommentFilename
    FROM studioProjects p
    
    INNER JOIN studioCompaniesProjects cp ON p.id = cp.project_id
    INNER JOIN studioCompanies c ON cp.company_id = c.id
    INNER JOIN studioUsers u ON u.company_id = cp.company_id AND u.id=19
    
    LEFT JOIN (
      SELECT f.project_id, f.id AS fileid, ft.title, cm.comment, cm.comment_date, cu.username
      FROM studioFileComments cm
      INNER JOIN studioFiles f ON cm.file_id = f.id
      INNER JOIN studioFileTitles ft ON f.file_title_id = ft.id
      INNER JOIN studioUsers cu ON cm.comment_by_id = cu.id
      ORDER BY cm.comment_date DESC
    ) AS fcm ON fcm.project_id = p.id
    
    GROUP BY p.id
    That's just for the file comments, I'll do the uploads bit next, but - that seems to work. Whether it's actually doing the job is the reason I'm putting it up here. Is it right?

    The bit I can't really get my head round is how it's matching them up. So the embedded bit gets me a list of all the comments (and who made them, and on what file), ordered by date. And then then you just match the project ID in that select to the project ID in the main select, and it'll grab the 'top' comment (ie: the last one to be posted) and match it up to the project in the main select? Really? That's dead clever if it does

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Spudhead
    ...and it'll grab the 'top' comment (ie: the last one to be posted) and match it up to the project in the main select? Really? That's dead clever if it does
    um, no

    it might appear to be working, but i think your GROUP BY is hiding a collosal inefficiency

    let's start at the beginning

    whatever you're retrieving, it has to be for just the one user, right?

    so that's one row to start with

    then you can join to the company for that user, because there's just the one

    now you can join to the projects for that single company, so there are multiple projects involved

    the query so far is --
    Code:
      FROM studioUsers AS u
    INNER
      JOIN studioCompanies AS c
        ON c.id = u.company_id
    INNER
      JOIN studioCompaniesProjects AS cp
        ON cp.company_id = c.id
    INNER
      JOIN studioProjects AS p
        ON p.id = cp.project_id
     WHERE u.id=19
    now we have files, comments, and activities, which is where it gets confusing

    could you say again what it is you want to retrieve?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2002
    Posts
    189
    Oh dear

    So... yes, for a given user ID I want to return a row for each project that they're associated with, through the company that they work for. Users are associated with a single company, which can be associated with one or more projects. A user will thus be associated with all projects that their parent company has.

    For each project that's returned, I want to get some information about:
    1. The last comment made on that project. A comment is associated with a single file, and a file is associated with a single project (and is uploaded by a single user). For the comment that's returned, I'd like to retrieve the username of whoever made it and the date it was made.
    2. The last file uploaded for that project. Again, I'd like to retrieve the username of whoever uploaded it, and the date it was uploaded.

    Hopefully that makes a little sense. In an effort to add some clarity, I've attached the complete CREATE TABLE statements for all nine tables in the application. Unfortunately that does bring a little extra complexity, as there's stuff I haven't mentioned yet - like file subcategories, and a basic file versioning feature - I think that those things can be safely ignored for the purposes of this query though.
    Attached Files Attached Files
    Last edited by Spudhead; 08-19-08 at 08:33.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT p.id AS projectID
         , p.project_name
         , fu.username     AS last_file_upload_by
         , f.upload_date   AS last_file_upload_date
         , cu.username     AS last_comment_by
         , fc.comment_date AS last_comment_date
      FROM studioUsers AS u
    INNER
      JOIN studioCompanies AS c
        ON c.id = u.company_id
    INNER
      JOIN studioCompaniesProjects AS cp
        ON cp.company_id = c.id
    INNER
      JOIN studioProjects AS p
        ON p.id = cp.project_id
    INNER
      JOIN ( SELECT project_id
                  , MAX(upload_date) AS last_upload
               FROM studioFiles
             GROUP
                 BY project_id ) AS last_f
        ON last_f.project_id = p.id
    INNER
      JOIN studioFiles AS f
        ON f.project_id = p.id
       AND f.upload_date = last_f.last_upload
    INNER
      JOIN studioUsers AS fu
        ON fu.id = f.user_id
    INNER
      JOIN ( SELECT studioFiles.project_id
                  , MAX(studioFileComments.comment_date) AS last_comment
               FROM studioFiles
             INNER
               JOIN studioFileComments
                 ON studioFileComments.file_id = studioFiles.id
             GROUP
                 BY project_id ) AS last_c
        ON last_c.project_id = p.id
    INNER
      JOIN studioFiles AS f2
        ON f2.project_id = p.id
    INNER
      JOIN studioFileComments AS fc
        ON fc.file_id = f2.id
       AND fc.comment_date = last_c.last_comment
    INNER
      JOIN studioUsers AS cu
        ON cu.id = fc.comment_by_id
     WHERE u.id=19
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2002
    Posts
    189
    Aha! So.. basically... do the GROUP BY in the sub-selects, rather than one big one at the end, and join those sub-selects to the file / user tables, rather than joining to the file / user tables inside the sub-selects.

    Thank you very much for all your help and advice

Posting Permissions

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