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

Spudhead · #1 (**permalink**) 08-14-08, 12:23

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.

r937 · #2 (**permalink**) 08-14-08, 12:58

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.

Spudhead · #3 (**permalink**) 08-14-08, 13:31

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

Spudhead · #4 (**permalink**) 08-18-08, 13:28

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

r937 · #5 (**permalink**) 08-18-08, 13:53

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?

Spudhead · #6 (**permalink**) 08-19-08, 06:23

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.

r937 · #7 (**permalink**) 08-29-08, 10:12

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

Spudhead · #8 (**permalink**) 09-01-08, 08:40

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

Internet Services

Web Development

Digital Marketing

Consumer Tech