How does a MYSQL Self-Join Work?

by

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.

More Related Contents:

  1. mysql_fetch_array() expects parameter 1 to be resource, boolean given in [closed]
  2. When I want to install MySQL I faced this issue “root account password” I forgot it
  3. How to perform grouped ranking in MySQL
  4. How to abort INSERT operation in MySql trigger?
  5. Restoring MySQL database from physical files
  6. Mysql or/and precedence?
  7. MySQL Trigger after update only if row has changed
  8. MySQL “WITH” clause
  9. MySQL Query – Records between Today and Last 30 Days
  10. MySQL “Group By” and “Order By”
  11. MySQL Query to select data from last week?
  12. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
  13. MySQL user DB does not have password columns – Installing MySQL on OSX
  14. Migrate from Oracle to MySQL
  15. Which is more efficient: Multiple MySQL tables or one large table?
  16. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  17. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  18. MySQL ‘user_id’ in where clause is ambiguous problem
  19. MySQL JSON: How to find object from key value
  20. MYSQL into outfile “access denied” – but my user has “ALL” access.. and the folder is CHMOD 777
  21. Advanced MySql Query: Update table with info from another table
  22. Commit data in a mysql container
  23. How do I count columns of a table
  24. Mysqldump launched by cron and password security
  25. MySQL Trigger cannot update table – getting ERROR 1442
  26. MySQL delete duplicate records but keep latest
  27. Why MySQL is giving error “Not allowed to return a result set from a function”?
  28. Where I can find a list of “mysqldump” exit codes?
  29. How to do unique constraint works with NULL value in MySQL
  30. Using OR in LIKE Query in MySQL to compare multiple fields

Leave a Comment