Create view from mysql self referential table and another table

Question

I have a table 'organization' with fields id and parent. parent refers to the same table (self referential key).

I have another table called 'user_organization', in which I have user_id and org_id. org_id is linked to id in 'organization' table.

Now I want to create a view, using these two tables which contains user_id and organization_id. Since member of one organization will be a member of all the child organizations, this view will contain extra values from actual user_organization table.

How can I create this view?

Answer 1

It seems, with only SQL select statement it is not possible.

https://stackoverflow.com/a/9056945/721597

But with the help of functions, I managed to do it.

I created a function called IsRelated(id, parent) -- which tells whether id is related to parent (if id = parent they are related):

CREATE FUNCTION `IsRelated`(`GivenID` VARCHAR(10), ParentId VARCHAR(10)) RETURNS boolean
BEGIN
    DECLARE related boolean; 
    DECLARE ch INT;

    SET related = FALSE;  
    IF ParentId = GivenID THEN
        SET related = TRUE;
    ELSE
        SET ch = GivenID; 
        myloop: WHILE ch IS NOT NULL DO
            SELECT IF(parent_org_id = ch, NULL, parent_org_id) INTO ch FROM
            (SELECT parent_org_id FROM organizations WHERE id = ch) A;
            IF ch IS NOT NULL AND ch = ParentId THEN
                SET related = TRUE; 
                LEAVE myloop;
            END IF;
        END WHILE; 
    END IF;
    RETURN related;
END

Then I create a view like this:

CREATE VIEW `v_all_orgs` AS SELECT o1.id AS org, o2.id AS related_to
FROM organizations o1
JOIN organizations o2 ON IsRelated(o1.id, o2.id)

With these two, I created my required view in question:

CREATE VIEW `v_all_user_orgs` AS SELECT DISTINCT user_organizations.user_id, v_all_orgs.org as org_id, user_organizations.created_ts
FROM user_organizations JOIN v_all_orgs ON v_all_orgs.related_to = user_organizations.org_id OR v_all_orgs.org = user_organizations.org_id
ORDER BY user_organizations.user_id, v_all_orgs.org