Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    3

    Unanswered: Selecting a value based on a value based on a value ... [etc]

    Hi all,

    The situation:
    What I'm trying to make is a permission system for a website.

    The question:
    What I am trying to do, and hope to be able to do with a view of some sort, is to create a virtual column or table (view, or anything else that might help me), that gives me a row of values based on the initial value that I put in the select statement.

    The table:
    CREATE TABLE mgap_ranks(id INT(5) NOT NULL AUTO_INCREMENT, name VARCHAR(265) NOT NULL, inherit VARCHAR(265), PRIMARY KEY (id), UNIQUE (name))

    The data:
    INSERT INTO mgap_ranks(name, inherit) VALUES('Guest', null), ('Admin', 'Guest'), ('Admin', 'Guest')

    The version:
    5.5.10


    Example:
    The following statement:
    select id from mgap_ranks where name = 'Admin' or inherit = 'Admin'
    Returns the values 1 and 2 (1 being Guest, 2 being Admin)

    Second example, and the question:
    TRUNCATE TABLE mgap_ranks
    INSERT INTO mgap_ranks(name, inherit) VALUES('r1', null), ('r2', 'r1'), ('r3', 'r2'), ('r4', 'r3'), ('r5', 'r4'), ('r6', 'r3'), ('r7', 'r6')

    What I want, is a statement or view or something, that returns the ID of the values that fall under the inheritance of the rank provided.

    If the value I provide is 'r2', I want the ID's of 'r2' and 'r1'
    If the value I provide is 'r5', I want the ID's of 'r5', 'r4', 'r3', 'r2' and 'r1'
    If the value I provide is 'r7', I want the ID's of 'r7', 'r6', 'r3', 'r2' and 'r1'


    The me acting sad part:
    Could anyone please help me with this? It is an issue I have spend some time on already, and one that I would like to solve soon before I have to make loops in my programming code. If the database can do it, it looks a lot better!


    Thanks in front
    Thank you already, even if you just tried it and didn't came up with a solution!


    With kind regards,
    "Master-Guy" Marcel

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how many levels? the examples you've shown have at most 5 levels, and you can accomplish what you want with 5 self-joins (if there are 6 levels, you'd need 6 self-joins, et cetera)

    if you have more than, say, 15 levels, then you should consider implementing the nested set model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    3
    The idea is to have it dynamically deternime it's own amount of levels.
    I've looked through the nested set model, but from what I understand of it, the use is not what I'm looking for here, since I only want a single parent, and a dynamic tree. Reorganizing the tree would be too much of an effort.

    Since the tree is dynamic, I can't say how many levels the structure would contain, so I cannot use a fixed number of self-joins.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    give the nested set model a try, it really will do what you're looking for

    (yes, a single parent -- it's a hierarchy model, after all)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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