Create multiple results from single row joining either 2 or 3 tables based off conditional of table1 results?

Question

First let me say that yes, this is a horrible way to have stored data, second, it isn't my fault :) I am trying to integrate with a 3rd party database to extract info which is stored in 3 tables, which really should have been in two AND stored where table 2 had a many to one relationship. Since that isn't the case, I have a puzzle to share.

Table one contains rows in which multiple values can be stored. Each row has codeid1-codeid20. These columns may contain a value, or a 0 (they are never null). They also have a corresponding codetype1-codetype20 which will be either 0 or 1.

If codetype1 is equal to 0, we go to table 2 and select description from the matching table1.codeid1=table2.id. If codetype1 equals 1, we now have to look at table3 and find where table1.codeid1=table3.id and then match table3.table2id=table2.id and return the description.

Here is the data structure:

table1
codeid1,codeid2,codeid3,...codeid20 ... codetype1,codetype2,codetype3,.....codetype20
18      13      1          33           0         0         1              1
13      21      45         0            0         1         0              0


table2
id,    description
13    Item 13 Description
15    Item 15 Description
17    Item 17 Description
18    Item 18 Description
21    Item 21 Description
28    Item 28 Description
45    Item 45 Description

table3
id,  table2id
1    15
33   17
21   28

The results I would be looking for would look like this:

rowid, description
1      Item 18 Description
1      Item 13 Description
1      Item 15 Description
1      Item 17 Description
2      Item 13 Description
2      Item 28 Description
2      Item 45 Description

I got started working with someone last night, but I had missed part of the complexity of my situation in not integrating table3. Like I said, fun puzzle... This gives me the relationship between the first 2 tables, but I am unsure how I can work in a 3rd table.

SELECT table1.rowid, table2.description
FROM table2
INNER JOIN table1 
ON table2.id=table1.codeie1
OR table2.id=table1.codeie2
...

The database is a Faircom C-Tree DB over an ODBC connection, which is generally compatible with Mysql statements including UNION, WITH, INTERSECT, EXISTS, JOIN... There is no PIVOT function.

https://docs.faircom.com/doc/sqlref/sqlref.pdf

Answer 1

Perhaps it will work with or rather than in:

where exists (select 1
              from table1 as t1
              where t2.id = t1.codeid1 or
                    t2.id = t2.codeid2 or
                    . . .
             );