Help with a join query please

[sheardben]

Hi

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.

table `equipment`
`equipmentid`, `name`, `otherdetails`

table `services`
`serviceid`, `equipmentid`, `date`, `service_description`,`upcoming`

Bear in mind though that a certain equipment may not have any past services or it may not have any upcoming services.

Thanks!

[r937]

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

[sheardben]

Thanks so much, works perfectly!!

[atomicnuke]

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?

[r937]

randy? no, i'm not, not at the moment...

ok, i gotta go lie down from laughing so hard...

[atomicnuke]

LOL, meant to say Rudy, not even sure why I said Randy, my apologies.

[r937]

scuze me, i just want to clarify, i was not laughing at you, but at my joke about being randy

inner selects are more intensive? actually, no they aren't

are you talking about the "comma style" join like SELECT ... FROM x , y WHERE ...

[atomicnuke]

Maybe nested selects are the better phrase...maybe, lol. Is there a general rule of when and when they shouldn't be used?

[r937]

ah, you mean subqueries

they can be efficient, and also not

first thing would be to understand the difference between correlated and non-correlated subqueries

as a general rule, correlated subqueries can be stinkers

[atomicnuke]

Okay, I'll look into those two types, thank you...Rudy!