SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query should give overlapping userid's

    The image says it all. I have a scedule with locations and timeslots. How can I retrieve the userid's that have had overlap with me.
    mysql_question.png

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    consider the following timeline diagram

    Mfrom = me start
    Mto = me end
    Ofrom = other user start
    Oto = other user end
    Code:
                    Mfrom          Mto          
                      |               |               
    1    Ofrom---Oto  |               |               
                      |               |               
    2          Ofrom--|--Oto          |               
                      |               |               
    3                 |  Ofrom---Oto  |               
                      |               |               
    4         Ofrom---|---------------|---Oto       
                      |               |               
    5                 |        Ofrom--|--Oto 
                      |               |               
    6                 |               |  Ofrom---Oto
    Code:
          
    SELECT other.location
         , other.userid
      FROM daTable AS me
    INNER
      JOIN daTable AS other
        ON other.location = me.location
       AND other.userid <> me.userid
       AND other.to >= me.from  /* eliminates case 1 */
       AND other.from <= me.to  /* eliminates case 6 */
     WHERE me.userid = 999 -- me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, that looks ok. I only forgot that I would like to retrieve the names corresponding to the userid's as well from another table. So I'm using tables "scedule" and "users" where sceduleUserID = userid. How can I implement that into the query

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please test my query thoroughly first, to ensure that it's working correctly on your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I figured it out myself, thanks. Only one question left. Is it also possible to only retrieve the userid's where the overlap is >= 5 days? (So in the image from the first post only userid: 789 will be selected)

  6. #6
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought I figured it out, but I didn't...
    How can I retrieve the names that belong to the userid's that are stored in another table profile (with fields userid=sceduleUserID).

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by brain View Post
    I thought I figured it out, but I didn't...
    How can I retrieve the names that belong to the userid's that are stored in another table profile (with fields userid=sceduleUserID).
    does my query work correctly on your data?

    please show layout of new table to be joined
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, it works correctly.
    This is what I have now:

    SELECT *, other.roosterInstelling, other.roosterUserID, other.roosterSpecialisme
    FROM content_Zorginstelling, content_Specialisme,content_Rooster AS me
    INNER
    JOIN content_Rooster AS other
    ON other.roosterInstelling = me.roosterInstelling AND other.roosterSpecialisme = me.roosterSpecialisme
    AND other.roosterUserID <> me.roosterUserID
    AND other.roosterTot >= me.roosterVan
    AND other.roosterVan <= me.roosterTot
    WHERE me.roosterUserID = '$userID' AND specialismeID = me.roosterSpecialisme AND zorgID = me.roosterInstelling


    If I echo the results (roosterUserID | specialismeNaam | instellingNaam):

    5767 | psychiatrie | GGZ inGeest
    29009 | psychiatrie | GGZ inGeest
    45677 | KNO-heelkunde | VU Medisch Centrum
    39417 | KNO-heelkunde | VU Medisch Centrum
    19607 | oogheelkunde | VU Medisch Centrum
    7792 | oogheelkunde | VU Medisch Centrum
    4529 | neurologie | Sint Lucas Andreas Ziekenhuis
    19064 | heelkunde/chirurgie | Spaarne Ziekenhuis, Hoofddorp
    5106 | heelkunde/chirurgie | Spaarne Ziekenhuis, Hoofddorp
    18237 | interne geneeskunde | Spaarne Ziekenhuis, Hoofddorp
    39966 | SEH-geneeskunde | Onze Lieve Vrouwe Gasthuis, locatie Oosterpark
    56880 | SEH-geneeskunde | Onze Lieve Vrouwe Gasthuis, locatie Oosterpark

    I've looked up the userid's in the table content_Profile and they are all correct. Those are the people I've met during my internships.
    How can I get the table content_Profile in the query to retract the names as well?

    table:content_Profile
    fields: profileUserID (which would have to match roosterUserID), profileNaam (the name of the person)

    FYI: rooster=scedule, instelling=location, specialisme=specialism, van=from, to=to, naam=name

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, don't mix comma-style joins with explicit JOIN syntax joins -- you'll get messed up

    here's your query, re-written --
    Code:
    SELECT * -- this is not a good idea !!!
         , other.roosterInstelling
         , other.roosterUserID
         , other.roosterSpecialisme
      FROM content_Rooster AS me
    INNER
      JOIN content_Rooster AS other
        ON other.roosterInstelling = me.roosterInstelling 
       AND other.roosterSpecialisme = me.roosterSpecialisme
       AND other.roosterUserID <> me.roosterUserID
       AND other.roosterTot >= me.roosterVan
       AND other.roosterVan <= me.roosterTot
    INNER
      JOIN content_Zorginstelling
        ON content_Zorginstelling.zorgID = me.roosterInstelling
    INNER
      JOIN content_Specialisme   
        ON content_Specialisme.specialismeID = me.roosterSpecialisme
     WHERE me.roosterUserID = '$userID'
    now it's straightforward to add another join
    Code:
    SELECT content_Profile.profileNaam 
         , other.roosterInstelling
         , other.roosterUserID
         , other.roosterSpecialisme
      FROM content_Rooster AS me
    INNER
      JOIN content_Rooster AS other
        ON other.roosterInstelling = me.roosterInstelling 
       AND other.roosterSpecialisme = me.roosterSpecialisme
       AND other.roosterUserID <> me.roosterUserID
       AND other.roosterTot >= me.roosterVan
       AND other.roosterVan <= me.roosterTot
    INNER
      JOIN content_Zorginstelling
        ON content_Zorginstelling.zorgID = me.roosterInstelling
    INNER
      JOIN content_Specialisme   
        ON content_Specialisme.specialismeID = me.roosterSpecialisme
    INNER
      JOIN content_Profile
        ON content_Profile.profileUserID = other.roosterUserID
     WHERE me.roosterUserID = '$userID'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool! Works like a charm ;-)
    As long as we're here... can I select based on the days of overlap?As mentioned above: overlap is >= 5 days? (So in the image from the first post only userid: 789 will be selected)

  11. #11
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can I also retrieve the start- and enddate from the overlap period?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by brain View Post
    can I select based on the days of overlap?
    absolutely, yes

    you have the endpoints (startdate and enddate) of both your internship and the "other" internship

    the simple date math i will leave up to you

    you might find the proprietary mysql LEAST and GREATEST functions useful to calculate the start and end of the period of actual overlap

    refer to the timeline diagram above, cases 2 through 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict brain's Avatar
    Join Date
    Sep 2001
    Location
    Amsterdam
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I will do some math. Thanks for the replies. I really apreciate it!


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
  •