Here are some resources which may be helpful: RailsCasts episode #163 Self-Referential Association regarding self-referential many-to-many relationships RailsCasts episode #47 Two Many-to-Many. This might be more relevant to what you’re attempting to accomplish A gist someone created for self-referential relationships using HABTM I’ll summarize the information found in those links: Given that you’re describing a … Read more
The reason why the employee-manager example is so common, is because it hits the nail on the head. A self join on a table looks for pairs of rows, like any join, but with both rows coming from the same table. Nothing special really.
You can view self-join as two identical tables. But in normalization, you cannot create two copies of the table so you just simulate having two tables with self-join. Suppose you have two tables: Table emp1 Id Name Boss_id 1 ABC 3 2 DEF 1 3 XYZ 2 Table emp2 Id Name Boss_id 1 ABC 3 … Read more
You use a self join when a table references data in itself. E.g., an Employee table may have a SupervisorID column that points to the employee that is the boss of the current employee. To query the data and get information for both people in one row, you could self join like this: select e1.EmployeeID, … Read more
(The following applies when every row is SQL DISTINCT, and outside SQL code similarly treats NULL like just another value.) Every base table has a statement template, aka predicate, parameterized by column names, by which we put a row in or leave it out. We can use a (standard predicate logic) shorthand for the predicate … Read more
WITH q AS ( SELECT * FROM mytable WHERE ParentID IS NULL — this condition defines the ultimate ancestors in your chain, change it as appropriate UNION ALL SELECT m.* FROM mytable m JOIN q ON m.parentID = q.PersonID ) SELECT * FROM q By adding the ordering condition, you can preserve the tree order: … Read more