CODEWITHSUNDEEP
sqlsql-server
xivo
xivo

Reputation: 2054

How to join a table with multiple foreign keys from the same table?

Stupid question, but I couldn't find the right search for this question and I'm having a mind block. I have a table with keys from a reference table and I want to avoid using a loop to select the records without having duplicates. I need to get Mode 1-5 joined so I can get the names without having duplicates. Please leave comments so I can clarify.

Mode(1-5) are ModeIDs

Example:

Table to Join

ID
Mode1
Mode2
Mode3
Mode4
Mode5

Reference Table

ModeID
ModeName

Upvotes: 1

Views: 138

Answers (2)

jbl
jbl

Reputation: 15413

I think unpivoting the table in a view would help overcome the table design on the long run for many of your select queries. ( Especially when someone will have the genius idea to addd a sixth mode :-)

CREATE VIEW UnpivotedTable AS
    SELECT 
        ID, Mode, ModeId 
    FROM 
        TableToJoin
    UNPIVOT (ModeId FOR mode IN (Mode1,Mode2,Mode3,Mode4,Mode5)) AS t
GO

Your query would then become a rather "classic" PIVOT query (but that's not really the point of my answer. The unpivotting view is)

select Id, Mode1, Mode2, Mode3, Mode4, Mode5
from (
    select 
        Id, Mode, ModeName 
    from 
        UnpivotedTable 
            inner join 
        ReferenceTable 
            on ReferenceTable.ModeId = UnpivotedTable.ModeId
        ) as sourcetable
    pivot (
        max(ModeName) 
        for Mode in (Mode1, Mode2, Mode3, Mode4, Mode5)
        ) as pivotTable

Hope this will help on the long run. Sorry for the late answer

Upvotes: 1

Shmiddty
Shmiddty

Reputation: 13967

This is the answer to your question:

SELECT 
    m1.ModeName,
    m2.ModeName,
    m3.ModeName,
    m4.ModeName,
    m5.ModeName
FROM Modes m
LEFT JOIN Ref m1 on m1.ModeID = m.Mode1
LEFT JOIN Ref m2 on m2.ModeID = m.Mode2
LEFT JOIN Ref m3 on m3.ModeID = m.Mode3
LEFT JOIN Ref m4 on m4.ModeID = m.Mode4
LEFT JOIN Ref m5 on m5.ModeID = m.Mode5

But I would suggest that there's a problem with your DB design. You should be using a join table.

Upvotes: 6

Related Questions