CODEWITHSUNDEEP
sqlsql-server
Mongolojdo
Mongolojdo

Reputation: 98

Query relations within the same table

I have a table of entities that can have many-to-many relations to each other, using a second junction table. At a first glance, the design may seem flawed and suggests a separate table for each type of entity. The thing is, that the entities are generic, and completely user-defined. They may also be connected completely ad-hoc and each entity can have an unlimited number of connections. Here is a simplified example of my tables:

Entities
------------
Entity  | Id | Type
-------------------
Event   | 1  | Request
Stroll  | 2  | Activity
Dinner  | 3  | Activity
Angela  | 4  | Person
Anders  | 5  | Person
Michael | 6  | Person

Junctions
----------------
Left    | Right
----------------
1       | 2    // Connect Request -> Stroll
2       | 4    // Connect Stroll -> Angela
1       | 3    // Connect Request -> Dinner
3       | 5    // Connect Dinner -> Anders
3       | 6    // Connect Dinner -> Michael

Now to my question:

I would like to perfom queries from the view-point of different entities. Lets say I would like to look at Requests and see what Activities they have, and any Persons attending each activity. I would like to get a result like this:

Request  | Activity | Person
-----------------------------
Event    | Stroll   | Angela
         | Dinner   | Anders
                    | Michael

I would also, for example, like to be able to flip the coin and look at Persons And see what Requests they attend, like this:

Person  | Request
-----------------
Angela  | Event
Anders  | Event
Michael | Event

How can i write queries to achieve results like this, and is it even possible with the current structure? I have spent much time on googling and trials with no luck, and I am very grateful for any help.

Here is an SQLFiddle

Upvotes: 1

Views: 80

Answers (1)

Alexander
Alexander

Reputation: 3179

That's how you do it

SELECT e1.Entity Request,
       e2.Entity Activity,
       e3.Entity Person
  FROM Junctions j1 
       JOIN Junctions j2 
            ON j1.`Right` = j2.`Left`
       JOIN Entities e1
            ON j1.`Left` = e1.Id
       JOIN Entities e2
            ON j1.`Right` = e2.Id
       JOIN Entities e3
            ON j2.`Right` = e3.Id;

SQLFiddle

To help you understand - at first I joined Junctions on itself like that:

SELECT j1.`Left` Request, 
       j1.`Right` Activity,  
       j2.`Right` Person  
  FROM Junctions j1 
       JOIN Junctions j2 
            ON j1.`Right` = j2.`Left`;

And then joined to the Entity, as you can see, to replace the Ids. One time for each type.

But, nevertheless, I still think that this architecture is horrible, and it needs to be redesigned.

Upvotes: 1

Related Questions