1

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:

enter image description here

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?

enter image description here

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)
0

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.

  • 1
    Sorry, I deleted my irrelevant comment. – Rick James Oct 2 '18 at 2:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.