How does a MYSQL Self-Join work?

advertisements

I recently asked a question about Self-Joins and I got a great answer.

The query is meant to find the ID, Start Date, and Price of Event2, Following Event1 by 1 Day.

The code WORKS fine. But I don't understand HOW.

Could someone explain as thoroughly as you can- what the different parts of the query are and what they do?

SELECT event2.id, event2.startdate, event2.price
FROM mm_eventlist_dates event1
JOIN mm_eventlist_dates event2
ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)
WHERE event1.id=$id

I really appreciate your help, for whatever reason I'm having a really hard time wrapping my head around this.


The way I'd try to understand this is to write out two lists on piece one labelled event1 and one event2. Then list a few records in each list (the lists will be identical) now start at the WHERE in the description below.

We're taking data from two tables (OK the same table used twice, but try to ignore that for the moment)

FROM mm_eventlist_dates event1
JOIN mm_eventlist_dates event2

It probably helps to read the rest from the bottom up.

  WHERE event1.id=$id

So we want the record from event1 that has the specified record id. Presumably that's exactly one record. Now we figure out the day after that event ended.

 date_add(event1.enddate, INTERVAL 1 DAY)

Now that tells us the records from event2, they need to start on that date,

ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)

We now have two records identified, what fields do we want?

SELECT event2.id, event2.startdate, event2.price

Oh, just the fields from the one whose start date we figured out.