user3442612 user3442612 - 2 months ago 6
MySQL Question

Adding a subquery to a join

I've been using the following join, to pull rows of users whom have volunteered for various project positions.

SELECT p.id, up.position_id, title, max_vol, current_datetime, IF(up.id IS NULL, "0", "1") volunteered
FROM positions AS p
LEFT JOIN users_positions AS up
ON p.id = up.position_id
AND up.user_id = 1
AND up.calendar_date = '2016-10-03'
WHERE
p.project_id = 1
AND p.day = 1


...but in a change of functionality, I have to now account for the date of the latest edit to a project. In another query, I solved it like so

SELECT *
FROM positions
WHERE
current_datetime = (SELECT MAX(current_datetime)
FROM positions
WHERE
project_id = 1 AND day = 1)


Which works fine, but now I have to also incorporate the return of rows which match the latest datetime in the left join query.

I just can't seem to wrap my head around it. Any suggestions? Thanks.

Answer

Use a sub query, like this:

SELECT
  p.id,
  up.position_id,
  title,
  max_vol,
  current_datetime,
  IF(up.id IS NULL,
  "0",
  "1") volunteered      
FROM
  (     SELECT
    *    
  FROM
    positions    
  WHERE
    current_datetime = (
      SELECT
        MAX(current_datetime)                  
      FROM
        positions                  
      WHERE
        project_id = 1            
        AND day = 1     
    )     
  ) AS p      
LEFT JOIN
  users_positions AS up            
    ON p.id = up.position_id            
    AND up.user_id = 1            
    AND up.calendar_date = '2016-10-03'         
WHERE
  p.project_id = 1                
  AND p.day = 1
Comments