How to query two values from the same table from different records combined with fields from other tables? I have a query similar to the one below which is getting data from a couple of tables and works as expected. The table eventsink.RedEvent has another column called Finished. I want to add the Finished column to the result of my query, however the value I want for Finished will be from another record than the record that Created is queried from. I need the MIN() of Finished that also matches DeviceId and also has an Id = '1'

Thanks for any help you can provide. I am pretty new to such complex SQL queries.

SELECT eventsink.RedEvent.DeviceId,
       MAX(eventsink.RedEvent.Created),
       BranchNumber,
       CountryCode
FROM eventsink.RedEvent
JOIN md.Elevator ON md.Elevator.DeviceId = eventsink.RedEvent.DeviceId
JOIN eventsink.RedMasterDataEvent ON eventsink.RedMasterDataEvent.DeviceId = eventsink.RedEvent.DeviceId
WHERE (Id = '9'
       AND SoftwareVersion LIKE '[1][.]%')
GROUP BY eventsink.RedEvent.DeviceId,
         BranchNumber,
         CountryCode
Try this:     

SELECT eventsink.RedEvent.DeviceId,
           MAX(eventsink.RedEvent.Created),
           BranchNumber,
           CountryCode
    FROM eventsink.RedEvent
    JOIN md.Elevator ON md.Elevator.DeviceId = eventsink.RedEvent.DeviceId
    JOIN eventsink.RedMasterDataEvent ON eventsink.RedMasterDataEvent.DeviceId = eventsink.RedEvent.DeviceId
    JOIN (SELECT MIN(Finished), DeviceId FROM eventsink.RedEvent WHERE Id = '1' GROUP BY DeviceId) t 
         ON eventsink.RedEvent.DeviceId = t.DeviceId
    WHERE (Id = '9'
           AND SoftwareVersion LIKE '[1][.]%')
    GROUP BY eventsink.RedEvent.DeviceId,
             BranchNumber,
             CountryCode


Alternatively, you can put the derived table (aliased as t) into a CTE. They are the same thing really.