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.

consider the following timeline diagram

Mfrom = me start
Mto = me end
Ofrom = other user start
Oto = other user end


                Mfrom          Mto          
                  |               |               
1    Ofrom---Oto  |               |               
                  |               |               
2          Ofrom--|--Oto          |               
                  |               |               
3                 |  Ofrom---Oto  |               
                  |               |               
4         Ofrom---|---------------|---Oto       
                  |               |               
5                 |        Ofrom--|--Oto 
                  |               |               
6                 |               |  Ofrom---Oto
      
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   

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

please test my query thoroughly first, to ensure that it’s working correctly on your data

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)

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

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

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 –


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


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
[COLOR="#0000FF"]INNER
  JOIN content_Profile
    ON content_Profile.profileUserID = other.roosterUserID[/COLOR]
 WHERE me.roosterUserID = '$userID'

Cool! Works like a charm :wink:
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)

Can I also retrieve the start- and enddate from the overlap period?

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

Ok, I will do some math. Thanks for the replies. I really apreciate it!