Hi Azedji,

The structure below I think has a bit more flexibility, you will see that you can add a role at any time, there may be better ways of doing the hierarchy select, but the structure will allow for the dynamic addition of new roles, new participants, and anything else you will need, the important table is the mapping table which allows you to say which participants were at which event, then your select handles the rest, you can copy the join for the event join and each time link it to the previous one, which will will take the hierarchy to the next level, for each parent. Like I said, the hierarchy may not be the ultimate solution, but at least your data will be in a much more dynamic structure? Comments welcome.

I don't know if my explanation is good enough, you are welcome to shoot with specific questions on which data you will have to put where to make it work if the structure is not self-explanatory enough,

CREATE TABLE [dbo].[Event]

(

[EventID] INT IDENTITY NOT NULL,

[EventName] VARCHAR(150) NULL

)

CREATE TABLE [dbo].[EventRole]

(

[EventRoleID] INT IDENTITY NOT NULL,

[EventRoleName] VARCHAR(150) NULL,

[EventRoleParentID] INT NULL

)

CREATE TABLE [dbo].[ParticipantMap]

(

[EventID] INT NOT NULL,

[ParticipantID] INT NOT NULL

)

CREATE TABLE [dbo].[Participants]

(

[ParticipantID] INT IDENTITY(1,1) NOT NULL,

[ParticipantName] VARCHAR(50) NOT NULL,

[EventRoleID] INT NOT NULL

)

INSERT INTO [dbo].[Event] VALUES ('Event1')

INSERT INTO [dbo].[Event] VALUES ('Event2')

INSERT INTO [dbo].[Event] VALUES ('Event3')

INSERT INTO [dbo].[Event] VALUES ('Event4')

INSERT INTO [dbo].[EventRole] VALUES ('Speaker',NULL)

INSERT INTO [dbo].[EventRole] VALUES ('Attendent','1')

INSERT INTO [dbo].[EventRole] VALUES ('Instructor',NULL)

INSERT INTO [dbo].[EventRole] VALUES ('Coordinator','3')

INSERT INTO [dbo].[Participants] VALUES ('Person1','1')

INSERT INTO [dbo].[Participants] VALUES ('Person2','1')

INSERT INTO [dbo].[Participants] VALUES ('Person3','2')

INSERT INTO [dbo].[Participants] VALUES ('Person4','2')

INSERT INTO [dbo].[Participants] VALUES ('Person5','3')

INSERT INTO [dbo].[Participants] VALUES ('Person6','3')

INSERT INTO [dbo].[Participants] VALUES ('Person7','4')

INSERT INTO [dbo].[ParticipantMap] VALUES ('1','1')

INSERT INTO [dbo].[ParticipantMap] VALUES ('1','3')

INSERT INTO [dbo].[ParticipantMap] VALUES ('1','4')

INSERT INTO [dbo].[ParticipantMap] VALUES ('1','5')

INSERT INTO [dbo].[ParticipantMap] VALUES ('2','1')

INSERT INTO [dbo].[ParticipantMap] VALUES ('2','2')

INSERT INTO [dbo].[ParticipantMap] VALUES ('3','3')

INSERT INTO [dbo].[ParticipantMap] VALUES ('3','4')

INSERT INTO [dbo].[ParticipantMap] VALUES ('3','5')

INSERT INTO [dbo].[ParticipantMap] VALUES ('3','6')

SELECT

EventName

, ParticipantName

, erL1.EventRoleName AS ParticipantRole

, erL2.EventRoleName AS ParticipantParentRole

FROM

[dbo].[Event] e

LEFT JOIN [dbo].[ParticipantMap] pm ON e.EventID = pm.EventID

LEFT JOIN [dbo].[Participants] p ON pm.ParticipantID = p.ParticipantID

LEFT JOIN [dbo].[EventRole] erL1 ON p.EventRoleID = erL1.EventRoleID

LEFT JOIN [dbo].[EventRole] erL2 ON erL1.EventRoleParentID = erL2.EventRoleID