SQL - joining on multiple columns in the same row

Question

Our school's ERP has a nasty database structure to it and since it is not normalized correctly, I have an issue with joining on the same table multiple times.

The DegreeHistory table has these columns in one row entry for a person:

|Major1|Major2|Major3|Minor1|Minor2|Minor3|
-------|------|------|------|------|------|
 CMPT                 BUSI

There is another table DegreeDescription:

|DegreeCode|DegreeDesc      |
-----------|-----------------
CMPT       |Computer Science
BUSI       |Business

I want a query shows a student's degree history information but skips the degree codes and shows the degree description instead. Is there a way I can do this other than:

SELECT dd.DegreeDesc, dd1.DegreeDesc FROM DegreeHistory dh
LEFT JOIN DegreeDescription dd ON dd.DegreeCode = dh.Major1
LEFT JOIN DegreeDescription dd1 ON dd1.DegreeCode = dh.Major2 ...

For each of the possible majors, minors, concentrations, certifications, etc... Seems like a large and ugly query (though simple to do).

Answer 1

You are in the right track, just a little change...:

SELECT dd.DegreeDesc, dd1.DegreeDesc FROM DegreeHistory dh
LEFT JOIN DegreeDescription ON DegreeCode = dh.Major1 dd
LEFT JOIN DegreeDescription ON DegreeCode = dh.Major2 dd1 ...

Certainly it's ugly but that's what you get with non-normalized structures :-(