Can you solve a Sudoku with only one SELECT statement? No temporary tables, SQL extensions, stored procedures or functions, not even non-relational features such as ORDER or GROUP BY — just plain joins and predicates? You can! With a combination of (admittedly quite a few) self joins.
Before we finally have a look at this rather exotic fun example however, we concentrate on the most practical uses of joining a table to itself — beyond the classical text-book example of the employee’s table.
There exist multiple classes of problems that can be solved elegantly with a self join. Solutions that are overlooked sometimes even by more experienced developers. The session will sharpen your eyes for these problems, without diving into theory, but with simple hands-on examples that should be easy to grasp with a sound understanding of basic SQL.
Beat Vontobel is the CTO at MeteoNews AG, a Swiss weather forecasting company, where he initiated the transition from MS SQL to MySQL and generally from closed to open source a few years ago. The company used most of the new features of MySQL 5.0 right from the start (and Beat Vontobel, an active member of the MySQL community and of the MySQL guilds, regularly blogged about this experience.
View a complete list of MySQL contacts.