CODEWITHSUNDEEP
mysqlsqljoinmultiple-tables
Adam
Adam

Reputation: 209

SQL join: counting rows and get most recent row from another table

Okay, so I've already got a fairly complex SQL statement to get my information, but I need to add something else to it.

Basically, the items get a count of the files and also a count of the types of files associated with them (amongst other things)...

I need to also get the ID of the latest file for floorplan and presentation AS another separate field

SELECT 
  items.*,
  designers.name AS designer,
  locations.name AS location,
  COUNT(DISTINCT files.id) AS files,
  SUM(files.type = 'floorplan') AS floorplans,
  SUM(files.type = 'presentation') AS presentations,
  SUM(files.type = 'photo') AS photos 
FROM
  items 
  LEFT JOIN designers 
    ON items.designer_id = designers.id 
  JOIN locations 
    ON items.location_id = locations.id 
  LEFT JOIN files 
    ON (items.id = files.item_id) 
WHERE items.location_id = 8 
GROUP BY items.id

I have looked at other QAs about joining the most recent row of another table, but they are based on having no joins to begin with and with my existing query I have no idea where to even begin. I still need the file counts and I can't join a table twice.

So to summarise, I need all that as well as the IDs for the most recent floorplan and most recent presentation. Files have a date_uploaded column containing a unix timestamp.

Upvotes: 1

Views: 142

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

If you need only file ids based on the file types you can use CASE in GROUP_CONCAT with ORDER BY and SUBSTRING_INDEX() to pick only only the latest one

SELECT ...,
 SUBSTRING_INDEX(
  GROUP_CONCAT(
    CASE
      WHEN files.type = 'floorplan'
      THEN files.id
      ELSE NULL
    END ORDER BY date_uploaded  DESC
  ) ,',',1) AS floorplans_fileid,
   SUBSTRING_INDEX(
  GROUP_CONCAT(
    CASE
      WHEN files.type = 'presentation'
      THEN files.id
      ELSE NULL
    END ORDER BY date_uploaded  DESC
  ) ,',',1) AS presentations_fileid
...
FROM ...

This way you can have the latest file id only not the row,based on the criteria

Upvotes: 1

Related Questions