Cacher is the code snippet organizer for pro developers

Self Referencing Table

• In the table below, a record (known as a category) contains a relationship: a parent category
• This column is called parent_ _id
  • if the value in this record is 0 then it doesn't contain a parent category
  • if the value is greater than 1, this means it refers to the id with that number
  • Record 3 has a parent_category_id of 2, meaning that it has a parent category with the id=2

Table: Categories

id   |   name    | parent_category_id
------------------------------------
  1  |           |      2             
  2  |           |      5             
  3  |           |      2             
  4  |           |      0            
  5  |           |      1            

• Here's the SQL statement that will produce a new table with the name of the category and its parent

SELECT p.name AS "Parent",
      s.name AS "Category"
FROM Categories p
LEFT JOIN Categories s 
ON s.category_parent_id = p.id

• Select the names of the columns
• LEFT JOIN the same table (produces full result)
• c will reference the category_parent_id and c2 will reference the id column

ALTERNATIVE

• You can self-reference the same table without the use of anotherJOIN by just using a SELECT statement

SELECT column_1,
    ( SELECT col_name FROM tble2 t2 WHERE t2.something_id = tble2.id ) #should return 1 record!
JOIN tble2 ON ..
FROM table