Im wondering if its possible to do what I want to do all in one query - Im guessing it is because i dont think its that complicated but its too complicated for me to figure out anytime soon.
I’ve got two tables, equipment and services:
Each equipment may have several services, and these services might be past services or upcoming services. There is a column in the services table upcoming which if set to 1 tells you that it is an upcoming service and if 0 a past service.
I want to select all from equipment, and along with each row select the date of last service and the date of the next upcoming service.
SELECT equipmentid
, name
, otherdetails
, ( SELECT MIN(date)
FROM services
WHERE equipmentid = equipment.equipmentid
AND upcoming = 1 ) AS next_service_date
, ( SELECT MAX(date)
FROM services
WHERE equipmentid = equipment.equipmentid
AND upcoming = 0 ) AS prev_service_date
FROM equipment
Randy, I have a question after seeing your response. I’ve read that inner selects are more intensive than normal queries. I also see from your posts that it isn’t something you usually do. When do you decide when and when you should not use them?