SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Self Joins

  1. #1
    SitePoint Member
    Join Date
    Aug 2001
    Location
    Swansea, South Wales, UK
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Self Joins

    Ok I'm working my way through the different type of joins possible with SQL. I can understand all but one, the self join. Can someone please give a nice easy example of why you would want to use a self join.
    signature

  2. #2
    SitePoint Zealot JEmLAC's Avatar
    Join Date
    Apr 2002
    Location
    Kansas City
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    self joins

    Self-joins are used when you have recursive-type relationships. I guess the classic example would be an employee table that holds all the employees of a company. The primary key, Employee_ID, identifies the employee uniquely on the table. Another field, Supervisor_ID, is the employee id of the employee's supervisor. Using this method you can have one table that holds all the employees (for uses that apply to all employees, HR activities, for example), yet allows a hierarchical view of the supervisory structure of the company. An enhancement to this rather simple structure is to add an "employee_class" or some such field that indicates whether the employee is a supervisor or not, or what level of supervisor. Be careful if you add more than one level of supervisor though. I generally don't recommend it, since it muddies the nice 1 to many relationship of a supervisor to reports. Of course, this model can be broken by an employee having multiple supervisors or ghost relationships, but that generally requires a bit more thought anyway.

    One caveat to this type of arrangement is that it requires a root or seed record which functionally sits at the top of the supervisory tree. I typically call it god, although that generally requires some documentary explanation. The record is usually not displayed, but it serves as the starting point for displaying the recursive relationship between supervisors and their direct reports.

    Hope that helps.

    B
    Morning person by habit, not by nature.

  3. #3
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you have a categories table that defines a maincat-subcat relationship, you'd probably have fields like:

    catID <- the pri key
    catName <- the name
    catParentID <- the parentId if (its a subcat)

    doing it this way, you can have as many layers of subcats as you need.

    sometimes, it makes sense to put a 0 in the catParentID if the record is a 'maincat'

    So to query the database for a maincat and its subcats, you'd probably use a

    WHERE catID = somevariable
    AND catParentID = catID

    or a self join then the WHERE....anyone know if one way is faster in general than the other?

    i'd still call the WHERE AND method a self-join though (conceptually at least)

  4. #4
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <humor>
    Finally! A way to organize my cats!
    </humor>

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,249
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by crowdozer
    <humor>...</humor>
    Where? Where?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  6. #6
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yah i guess its not that funny. but hey i only got 3 hours of sleep last night thanks to episode 2. basically i was joking about nagrom's example of "maincats" and "subcats" which made me think of trying to organize a large feline collection and how unpractical of an example. yes, i'm aware cat is just an abbreviation for category, but i found this amusing nonetheless. ok enough rambling.

  7. #7
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey i thought it was funny!

    i have a 30lb. main-cat. *really* i have to post a picture of him somewhere....

    but he doesn't need no stinkin parent_id column. the relationships to the sub-cats is clear.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •