Not sure how to go about joining these two tables

Question

I had trouble coming up with a title for this question because I'm not really sure how to do what I want, so I'm not sure if "joining" is the best terminology in this instance.

I have two tables in my database, Plans and Assumptions that look like the following:

Plans Table

Plankey  ass1 ass2 ass3 ass4
aplan    0    6    5    7
bplan    2    0    7    4

Assumptions Table

assType refKey assName
"ass1"  0      "gender factors a"
"ass1"  2      "gender factors b"
"ass2"  0      "age factors a"
"ass2"  6      "age factors b"
"ass3"  5      "inflation factors a"
"ass3"  7      "inflation factors b"
"ass4"  4      "tax factors a"
"ass4"  7      "tax factors b"

I need to make a query (or set of queries and sub-queries) that gives me the names of the assumptions in use for each plan:

Plankey  assName1           assName2         assName3               assName4
aplan    "gender factors a" "age factors b"  "inflation factors a"  "tax factors b"
bplan    "gender factors b" "age factors a"  "inflation factors b"  "tax factors a"

Yeah... I know. assName. Also, even if this isn't the best design, that is beyond my control. I'm just trying to query an existing set of data.

I should also mention there are over 500 assumption types (ass1,ass2,...,ass500,etc) and each type could have over 100 assumption refKey/Names per assumption type.

I'm trying to wrap my head around this and it seems easy, but I just can't figure out how to do it. Any ideas? Maybe there is a concept I'm missing because I just haven't encountered it yet. I'm okay with hardcoding the column names assName1, assName2 etc into my query, but even then I'm unsure how to "lookup" the assNames from the Assumptions table when it seems like I'm looking up from the same table for multiple columns in my result.

EDIT: I ommitted something really important. refkey is re-used in the Assumptions Table. So an assName is uniquely determined by the combination of assType and refKey. I apologize for not making that clear in my example! I forgot about that until I looked at the answers. I have changed my example to reflect this as well.

EDIT2: I am using MS SQL Server.

EDIT3: I expect to find match in the assumptions table for every plan. If not, I would have bigger problems - unrelated to this question though.

Answer 1

You have to LEFT JOIN onto the ASSUMPTIONS table for every ass# column in the PLANS table:

   SELECT p.plankey,
          a1.assname,
          a2.assname,
          a3.assname,
          a4.assname
     FROM PLANS p
LEFT JOIN ASSUMPTIONS a1 ON a1.refkey = p.ass1
                        AND a1.asstype = 'ass1'
LEFT JOIN ASSUMPTIONS a2 ON a2.refkey = p.ass2
                        AND a2.asstype = 'ass2'
LEFT JOIN ASSUMPTIONS a3 ON a3.refkey = p.ass3
                        AND a3.asstype = 'ass3'
LEFT JOIN ASSUMPTIONS a4 ON a4.refkey = p.ass4
                        AND a4.asstype = 'ass4'

Without knowing the database, I can't provide the syntax for dynamic SQL to construct the query for a varying number of joins that need to be performed.

Answer 2

SELECT p.PlanKey, a1.name, a2.name, a3.name, a4.name
FROM Plans AS p
LEFT JOIN Assumptions AS a1 ON p.ass1 = a1.refKey
LEFT JOIN Assumptions AS a2 ON p.ass2 = a2.refKey
LEFT JOIN Assumptions AS a3 ON p.ass3 = a3.refKey
LEFT JOIN Assumptions AS a4 ON p.ass4 = a4.refKey