Sql-server – Converting properties to columns

I have a list of types:

SELECT * FROM type;

id          name
----------- --------------------------------------------------
1           person
2           other god
3           location
4           role
5           gender

And a list of objects each of which has a type:

SELECT * FROM object;

id          name                                               type_id
----------- -------------------------------------------------- -----------
1           Adam                                               1
2           Eve                                                1
3           Cain                                               1
4           Abel                                               1
5           Jeroboam                                           1
6           Zeredah                                            3

And a view to show the type names:

SELECT * FROM object_view;

id          name                                               type_name
----------- -------------------------------------------------- --------------------------------------------------
1           Adam                                               person
2           Eve                                                person
3           Cain                                               person
4           Abel                                               person
5           Jeroboam                                           person
6           Zeredah                                            location

A list of relationships kinds:

SELECT * FROM relationship;

id          name
----------- --------------------------------------------------
1           has father
2           has mother
3           from

And a list of relationships between objects:

SELECT * FROM object_relationship;

object_a_id relationship_id object_b_id
----------- --------------- -----------
4           1               1
3           2               2
5           3               6

as well as a view of these relationships:

SELECT * FROM object_relationship_view;

object_a                                           relationship                                       object_b
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Abel                                               has father                                         Adam
Cain                                               has mother                                         Eve
Jeroboam                                           from                                               Zeredah

I'd like to list each object with a column for father, mother, and from. If an object doesn't have one of these properties, the column should display NULL. So the result should look like this:

Here's one approach that appears to work:

SELECT      id,

            object.name,

            (
                SELECT      (SELECT name FROM object WHERE id = object_b_id)
                FROM        object_relationship 
                WHERE       object.id = object_relationship.object_a_id AND 
                            object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'has father')
            ) AS father,

            (
                SELECT      (SELECT name FROM object WHERE id = object_b_id)
                FROM        object_relationship 
                WHERE       object.id = object_relationship.object_a_id AND 
                            object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'has mother')
            ) AS mother,

            (
                SELECT      (SELECT name FROM object WHERE id = object_b_id)
                FROM        object_relationship 
                WHERE       object.id = object_relationship.object_a_id AND 
                            object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'from')
            ) AS [from]

FROM        object;

My question is this: can this be done via JOIN?

This approach is close:

SELECT      object.name,
            (SELECT name FROM object WHERE id = REL_FATHER.object_b_id) AS father,
            (SELECT name FROM object WHERE id = REL_MOTHER.object_b_id) AS mother,
            (SELECT name FROM object WHERE id = REL_FROM.object_b_id)   AS [from]

FROM        object
            LEFT JOIN   object_relationship AS REL_FATHER   ON  object.id = REL_FATHER.object_a_id
            LEFT JOIN   object_relationship AS REL_MOTHER   ON  object.id = REL_MOTHER.object_a_id
            LEFT JOIN   object_relationship AS REL_FROM     ON  object.id = REL_FROM.object_a_id

WHERE       REL_FATHER.relationship_id = (SELECT id FROM relationship WHERE name = 'has father')    AND
            REL_MOTHER.relationship_id = (SELECT id FROM relationship WHERE name = 'has mother')    AND
            REL_FROM.relationship_id   = (SELECT id FROM relationship WHERE name = 'from');

The issue with that approach is that it only lists objects which have values for father, mother and from. If any of these are `NULL, they are not listed.

So, for example, if you have relationship data added by the following:

EXEC insert_object_relationship 'Abel', 'has father', 'Adam';
EXEC insert_object_relationship 'Abel', 'has mother', 'Eve';
EXEC insert_object_relationship 'Abel', 'from',       'Eden';

EXEC insert_object_relationship 'Cain', 'has father', 'Adam';
EXEC insert_object_relationship 'Cain', 'has mother', 'Eve';
EXEC insert_object_relationship 'Cain', 'from',       'Eden';

EXEC insert_object_relationship 'Jeroboam', 'from', 'Zeredah';

The above query returns the following:

(Note that Zeredah is not listed, because that entry does not have relationships for father and mother.

Is there an approach that is better than either of those shown above?

I'm sure the technique described above it not new; any pointers to references that discuss this are welcome. (I.e. is there a name for this in database theory texts?)

All of the code needed to generate these tables and data are included below.

If you feel that this question is better suited to stackoverflow, let me know and I'll ask over there.

Thanks for any suggestions!

DROP TABLE IF EXISTS object_relationship;
DROP TABLE IF EXISTS object;
--------------------------------------------------------------------------------
DROP TABLE IF EXISTS type;

CREATE TABLE type
(
    id      INT             NOT NULL    PRIMARY KEY     IDENTITY(1, 1),
    name    nvarchar(50)    NOT NULL
);
--------------------------------------------------------------------------------
CREATE TABLE object
(
    id      INT             NOT NULL    PRIMARY KEY     IDENTITY(1, 1),
    name    nvarchar(50)    NOT NULL,
    type_id int             NOT NULL    CONSTRAINT FK_object_type FOREIGN KEY REFERENCES type(id)
);
--------------------------------------------------------------------------------
DROP TABLE IF EXISTS relationship;

CREATE TABLE relationship
(
    id      INT             NOT NULL    PRIMARY KEY     IDENTITY(1, 1),
    name    nvarchar(50)    NOT NULL
);
--------------------------------------------------------------------------------
CREATE TABLE object_relationship
(
    object_a_id     INT                 CONSTRAINT FK_object_relationship_object_object_a   FOREIGN KEY REFERENCES object(id),
    relationship_id INT                 CONSTRAINT FK_object_relationship_relationship      FOREIGN KEY REFERENCES relationship(id),
    object_b_id     INT                 CONSTRAINT FK_object_relationship_object_object_b   FOREIGN KEY REFERENCES object(id)
);
--------------------------------------------------------------------------------
DROP VIEW IF EXISTS object_view;
GO

CREATE VIEW object_view

AS

SELECT  object.id, object.name AS name, type.name AS type_name
FROM    object INNER JOIN type ON object.type_id = type.id;

GO
--------------------------------------------------------------------------------
DROP VIEW IF EXISTS object_relationship_view;
GO

CREATE VIEW object_relationship_view

AS

SELECT      A.name AS object_a, relationship.name AS relationship, B.name AS object_b
FROM        object AS A INNER JOIN object_relationship ON A.id = object_relationship.object_a_id
                        INNER JOIN relationship ON object_relationship.relationship_id = relationship.id
                        INNER JOIN object AS B ON B.id = object_relationship.object_b_id;
GO
--------------------------------------------------------------------------------
INSERT INTO type (name)
VALUES
('person'),
('other god'),
('location'),
('role'),
('gender');
DROP PROC IF EXISTS insert_object;      

GO

CREATE PROC insert_object
    @object     AS nvarchar(50),
    @type       AS nvarchar(50)

AS

INSERT INTO object (name, type_id)
VALUES
(@object, (SELECT id FROM type WHERE name = @type));

GO
--------------------------------------------------------------------------------
EXEC insert_object 'Adam',      'person';
EXEC insert_object 'Eve',       'person';
EXEC insert_object 'Cain',      'person';
EXEC insert_object 'Abel',      'person';
EXEC insert_object 'Jeroboam',  'person';
EXEC insert_object 'Zeredah',   'location';
EXEC insert_object 'Eden',  'location';
--------------------------------------------------------------------------------
INSERT INTO relationship (name)
VALUES
('has father'),
('has mother'),
('from');
--------------------------------------------------------------------------------
DROP PROC IF EXISTS insert_object_relationship;
GO

CREATE PROC insert_object_relationship
    @a  AS nvarchar(50),
    @relationship AS nvarchar(50),
    @b AS nvarchar(50)

AS

INSERT INTO object_relationship (object_a_id, relationship_id, object_b_id)
VALUES
((SELECT id FROM object WHERE name = @a), (SELECT id FROM relationship WHERE name = @relationship), (SELECT id FROM object WHERE name = @b));

GO
--------------------------------------------------------------------------------
EXEC insert_object_relationship 'Abel', 'has father', 'Adam';
EXEC insert_object_relationship 'Cain', 'has mother', 'Eve';
EXEC insert_object_relationship 'Jeroboam', 'from', 'Zeredah';