CREATE TABLE dbo.MedDRANames (
    [Key]       int             not null,
    [Value]     varchar(100)    null,
    CONSTRAINT [PK_MedDRANames] PRIMARY KEY CLUSTERED ([Key])
    );

CREATE TABLE dbo.MedDRARelations (
    [SOC]       INT            NOT NULL,
    [HLGT]      INT            NOT NULL,
    [HLT]       INT            NOT NULL,
    [PT]        INT            NOT NULL,
    [LLT]       INT            NOT NULL,
    CONSTRAINT [PK_MedDRARelations] PRIMARY KEY CLUSTERED ([SOC] ASC, [HLGT] ASC, [HLT] ASC, [PT] ASC, [LLT] ASC),
    CONSTRAINT [FK_MedDRANameSOC] FOREIGN KEY ([SOC]) REFERENCES dbo.MedDRANAmes([Key]),
    CONSTRAINT [FK_MedDRANameHLGT] FOREIGN KEY ([HLGT]) REFERENCES dbo.MedDRANAmes([Key]),
    CONSTRAINT [FK_MedDRANameHLT] FOREIGN KEY ([HLT]) REFERENCES dbo.MedDRANAmes([Key]),
    CONSTRAINT [FK_MedDRANamePT] FOREIGN KEY ([PT]) REFERENCES dbo.MedDRANAmes([Key]),
    CONSTRAINT [FK_MedDRANameLLT] FOREIGN KEY ([LLT]) REFERENCES dbo.MedDRANAmes([Key])
    );

SELECT
        [X].[SOC] AS [Código SOC],
        [SOC].[Value] AS [Término SOC],
        [X].[HLGT] AS [Código HLGT],
        [HLGT].[Value] AS [Término HLGT],
        [X].[HLT] AS [Código HLT],
        [HLT].[Value] AS [Término HLT],
        [X].[PT] AS [Código PT],
        [PT].[Value] AS [Término PT],
        [X].[LLT] AS [Código LLT],
        [LLT].[Value] AS [Término LLT]
    FROM dbo.MedDRARelations AS [X]
    INNER JOIN dbo.MedDRANames AS [SOC] ON [X].[SOC] = [SOC].[Key]
    INNER JOIN dbo.MedDRANames AS [HLGT] ON [X].[HLGT] = [HLGT].[Key]
    INNER JOIN dbo.MedDRANames AS [HLT] ON [X].[HLT] = [HLT].[Key]
    INNER JOIN dbo.MedDRANames AS [PT] ON [X].[PT] = [PT].[Key]
    INNER JOIN dbo.MedDRANames AS [LLT] ON [X].[LLT] = [LLT].[Key]

SELECT
        [X].[SOC] AS [Código SOC],
        dbo.FindMedDRA(x.SOC) AS [Término SOC],
        [X].[HLGT] AS [Código HLGT],
        dbo.FindMedDRA(x.HLGT) AS [Término HLGT],
        [X].[HLT] AS [Código HLT],
        dbo.FindMedDRA(x.HLT) AS [Término HLT],
        [X].[PT] AS [Código PT],
        dbo.FindMedDRA(x.PT) AS [Término PT],
        [X].[LLT] AS [Código LLT],
        dbo.FindMedDRA(x.LLT) AS [Término LLT]
    FROM dbo.MedDRARelations AS [X]

CREATE FUNCTION [dbo].[FindMedDRA]
(
    @code int
)
RETURNS varchar(100)
AS
BEGIN
    DECLARE @returning varchar(100)

    SELECT @returning = dbo.MedDRANames.Value from dbo.MedDRANames where dbo.MedDRANames.[Key] = @code

    RETURN @returning
END