Mysql getting first and last value from timestamp column

Question

I am not getting the right result from my below query:

SELECT t.id as TaskId, cl.name as ClientName, p.name as ProjectName
     , t.name as TaskName, tp.name as TaskType
     , t.createdate as DateCreated,t.enddate as DueDate
     , u.firstname as TaskOwner, pr.name as Priorities
     , t.hours as AllotedHours, t.workedhours as LoggedHours
     , tl.firstEntry, tl.lastEntry
FROM tasks t
inner join ( 
    select distinct tasks_id,complete, createdate
        ,  min(createdate) as firstEntry, max(createdate) as lastEntry
    from tasklogs 
    WHERE complete in (90) 
      and createdate > '2018-08-25' 
    GROUP BY createdate
) AS tl 
    ON tl.tasks_id = t.id
inner join projects p 
    on p.id = t.projects_id
inner join tasktypes tp 
    on tp.id = t.tasktypes_id
inner join users u 
    on u.id = t.users_id
inner join priorities pr 
    on pr.id = t.priorities_id
inner join clients cl 
    on cl.id = p.clients_id
where p.projecttypes_id=1 ;

I am supposed to get a single result with firstEntry (first log) and lastEntry (last log) columns, but I'm getting multiple rows for each selection:

As you see in the below image I am getting 3 rows, I need one row with highlighted First entry of 90 and last entry of 90 complete. Maybe my query is the wrong way to do that?

Answer 1

full answer required more understanding of all tables structure, but You need start from debug Your inner query:

select 
    distinct tasks_id,
    complete, 
    createdate,  
    min(createdate) as firstEntry, 
    max(createdate) as lastEntry 
from tasklogs 
WHERE complete in (90) and createdate > '2018-08-25' 
GROUP BY createdate

it wrong (it wrong and with sql_mode='', and total), with GROUP BY createdate, all 3:

• createdate
• min(createdate)
• max(createdate)

would be the same

as variant - may be You need GROUP BY tasks_id, and include only 3 column:

• task_id
• min(createdate)
• max(createdate)

Answer 2

I agree with @a_vlad, you need to get this part right:

inner join ( 
    select distinct tasks_id,complete, createdate
        ,  min(createdate) as firstEntry
        , max(createdate) as lastEntry
    from tasklogs 
    WHERE complete in (90) 
      and createdate > '2018-08-25' 
    GROUP BY createdate
) AS tl  

First, distinct is redundant with group by (eventhough your group by is invalid)

inner join ( 
    select tasks_id, complete, createdate
        , min(createdate) as firstEntry
        , max(createdate) as lastEntry
    from tasklogs 
    WHERE complete in (90) 
      and createdate > '2018-08-25' 
    GROUP BY createdate
) AS tl  

Now, all you are using from t1 is tasks_id, firstEntry, lastEntry so we can transform it into a valid construction:

inner join ( 
    select tasks_id
        , min(createdate) as firstEntry
        , max(createdate) as lastEntry
    from tasklogs 
    WHERE complete in (90) 
      and createdate > '2018-08-25' 
    GROUP BY task_id
) AS tl  

Replace your t1 with that one and see if it helps. Note that I replaced createdate with task_id in the GROUP BY clause.