Fetching 1 row of main table with diverse max()s, min()s, and same-row-as-the-min() values from related tables

SELECT 
  t1.tasknum
, t1.description
, t1.refid
, t1.sysdesc
, t2.stepno
, t2.stepdetail
, t2.approvaldate
, MIN(t3.startdate) AS min_date1
, MIN(t3.enddate) AS min_date2

FROM              TABLE1 t1
  LEFT OUTER JOIN TABLE2 t2 ON t1.tasknum = t2.tasknum 
                           AND t2.stepno = (
                                 SELECT MIN(stepno)
                                 FROM TABLE2
                                 WHERE tasknum = t2.tasknum
                               )

  LEFT OUTER JOIN TABLE3 t3 ON t1.refid = t3.id

WHERE t1.sysdesc LIKE 'Apple'"""

GROUP BY 
  t1.tasknum
, t1.description
, t1.refid
, t1.sysdesc
, t2.stepno
, t2.stepdetail
, t2.approvaldate

table1

table2

table3

Conditions:

• 1st: all line items from table1 which contain 'Apple' as sysdesc.
• 2nd: min stepno, stepdetail for given tasknum without approval date for each tasknum
• 3rd: max step for given tasknum with approval date for each tasknum
• 4th: min start date and end date for each id.

Sample output required is shown below for tasknum = 1:

https://dbfiddle.uk/0-x8Rzzz

Please find the fiddle which is working but not returning results as expected.

Fiddle gives result of what I have accomplished but it is giving incorrect results.

I'm unable to address conditions 2 & 3 mentioned.

In your current query you have to differentiate the "approved" from the "non approved" dates,
thus have two tables instead of t2, each one with its dedicated criteria (AND approvaldate IS NULL / AND approvaldate IS NOT NULL).

  …
  LEFT OUTER JOIN TABLE2 t2 ON t1.tasknum = t2.tasknum 
                           AND t2.stepno = (
                                 SELECT MIN(stepno)
                                 FROM TABLE2
                                 WHERE tasknum = t2.tasknum
                                 AND approvaldate IS NULL -- ← This line added to t2
                               )
  LEFT OUTER JOIN TABLE2 tapp ON t1.tasknum = tapp.tasknum 
                           AND tapp.stepno = (
                                 SELECT MAX(stepno)
                                 FROM TABLE2
                                 WHERE tasknum = t2.tasknum
                                 AND approvaldate IS NOT NULL
                               )
  …

(see it integrated with your data and the rest of your query (first query))

Group as soon as possible

… But I would suggest that you directly reduce your joins of t2, t3 and tapp to 1-row (per t1.id), instead of double-joining to them (once to get the min / max, once to select the complete row corresponding to this min / max), so that you don't even have to puzzle on "do I need a GROUP BY or not?".

• when you simply need direct aggregates over a table, use them (both MIN() for t3)
• in the more complex case of a related column ("the stepdetail corresponding to the highest stepno"), use an ORDER BY stepno and limit the resultset to 1 using TOP 1
• note that, for the approval date, as you only need 1 column of that correlated table, you can include it directly as a SELECTed column

SELECT 
  t1.tasknum
, t1.description
, t1.refid
, t1.sysdesc
, t2.stepno
, t2.stepdetail
, (
     SELECT TOP 1 approvaldate -- Or SELECT MAX(approvaldate) with no ORDER BY, depending if we want the "latest approval" or "approval of the most advanced step" (which probably are the same, but what to do when they are not?), see @charlieface's comment.
     FROM TABLE2
     WHERE TABLE2.tasknum = t1.tasknum
     AND TABLE2.approvaldate IS NOT NULL
     ORDER BY stepno DESC
  ) AS approvaldate
, t3.min_date1
, t3.min_date2

FROM TABLE1 t1
  OUTER APPLY
  (
     SELECT TOP 1 stepno, stepdetail
     FROM TABLE2
     WHERE TABLE2.tasknum = t1.tasknum
     AND TABLE2.approvaldate IS NULL
     ORDER BY stepno
  ) t2
  OUTER APPLY -- Or CROSS APPLY if we want to discard tasks with no step approved yet, see @charlieface's comment.
  (
    SELECT MIN(startdate) min_date1, MIN(enddate) min_date2
    FROM TABLE3 t3
    WHERE t1.refid = t3.id
  ) t3
WHERE t1.sysdesc LIKE 'APPLE';

(running as the second query of the fiddle)