Re: Joining one table twice -> getting value^2
Posted by: laptop alias
Date: April 25, 2012 10:53AM
A. Read the Stickies at the top of the forum. Sometimes they disappear, in which case...
If your question concerns tables and data:
Within [ code ][ /code ] tags (leave off the spaces)...
1. Provide CREATE TABLE statements for each of the relevant tables
2. Provide a small but representative dataset for each of the tables, as a set of INSERT statements
3. Provide the resultset you'd expect from your query.
4. Provide the result of SELECT VERSION();
B. Group-wise Max queries
One frequently asked question relates to finding the Top-N of an aggregated result, one of a set of so-called 'GROUP-WISE MAX' queries.
Various solutions to this problem are provided at the following links:
C. Pivot Tables
A second frequently asked question involves the transposition of rows to columns, often involving the aggregation of data, so-called 'Pivot Table' queries. Although my preferred solution is to handle the transposition at the application level, e.g. with a bit of PHP, there is excellent discussion on this topic at www.artfulsoftware.com/infotree/queries.php?&bw=1179#523.
D. Hierarchical Data
A third frequently asked question relates to handling recursion in connection with hierarchical data.
Choices include, but are not limited to, the following:
- Join the table to itself as many times as could possibly be required
- Handle the recursion at the application level, e.g. with a PHP loop <-- my preference
- Use a Stored Procedure to handle the recursion
- Switch to a Nested Set (or some sort of hybrid model) instead of the 'so-called' Adjacency List model
All of these solutions are discussed more thoroughly elsewhere, including a number of excellent, popular, and hence easily googled, articles on Hierarchical data and MySQL.