SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with a join query please

    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.

    Code:
    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!
    -------------------------------
    http://www.divinglogs.net
    The free online diving community

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much, works perfectly!!
    -------------------------------
    http://www.divinglogs.net
    The free online diving community

  4. #4
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Half way to nowhere

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    randy? no, i'm not, not at the moment...

    ok, i gotta go lie down from laughing so hard...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOL, meant to say Rudy, not even sure why I said Randy, my apologies.
    Half way to nowhere

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe nested selects are the better phrase...maybe, lol. Is there a general rule of when and when they shouldn't be used?
    Half way to nowhere

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I'll look into those two types, thank you...Rudy!
    Half way to nowhere


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •