2

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:

enter image description here

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:

enter image description here

(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';

New contributor
dharmatech is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2

I'm sure the technique described above it not new...

Right, I think the term you're looking for is "pivot." You can use the T-SQL PIVOT operator to do this:

SELECT 
    pivot_table.aName,
    pivot_table.[has mother],
    pivot_table.[has father],
    pivot_table.[from]
FROM
(
    SELECT 
        oA.[name] AS aName, 
        oB.[name] AS bName, 
        r.[name] AS rName
    FROM dbo.[object] oA
    LEFT JOIN dbo.object_relationship ore
        ON ore.object_a_id = oA.id
    LEFT JOIN dbo.relationship r
        ON r.id = ore.relationship_id
    LEFT JOIN dbo.[object] oB
        ON ob.id = ore.object_b_id
) source_table
PIVOT
(
    MAX(bName)
    FOR rName IN ([has mother], [has father], [from])
) AS pivot_table
ORDER BY pivot_table.aName;

And the results with the sample data given in the script at the bottom of your post:

screenshot of query results in SSMS

  • Amazing answer. Thanks Josh! – dharmatech Mar 23 at 21:39
  • 1
    I had just recently seen PIVOT and it did cross my mind since this transformation is going from rows to columns. Very cool that you were able to demonstrate this! – dharmatech Mar 23 at 21:45
  • I found your wishlist on your profile. You should be receiving 'Foundation and Earth by Isaac Asimov' by April 21st. Thanks again! – dharmatech Mar 23 at 21:55
  • Wow, thanks @dha! I'm glad to have helped 😊 – Josh Darnell Mar 23 at 22:40
1

Here's an another approached based on Josh's answer above.

SELECT *
FROM    object_relationship_view
PIVOT
(
    MAX(object_b) FOR relationship IN ([has mother], [has father], [from])
) AS PIVOT_TABLE
ORDER BY object_a;

Instead of the subquery, it uses object_relationship_view.

In this case, the output is slightly different in that rows with NULL in all the columns are not output:

enter image description here

New contributor
dharmatech is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

dharmatech is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.