MySQL special case pivot

Question

I cant find an answer to this despite looking for several days!

In MySQL I have 2 Tables

ProcessList contains foreign keys all from the process Table

ID  |Operation1|Operation2|Operation3|etc....
---------------------------------------
1   |   1      |    4     |    6    | ....
---------------------------------------
2   |   2      |    4     |     5    |....
---------------------------------------
.
.
.

Process Table
ID | Name
-------------------
1  | Quote
2  | Order
3  | On-Hold
4  | Manufacturing
5  | Final Inpection
6  | Complete

Now, I am new to SQL but I understand that MYSQL doesnt have a pivot function as Ive researched, and I see some examples with UNIONs etc, but I need an SQL expression something like (pseudocode)

SELECT name FROM process
(IF process.id APPEARS in a row of the ProcessList)
WHERE processListID = 2

so I get the result

Order
Manufacturing
Final Inspection

I really need the last line of the query to be

WHERE processListID = ?

because otherwise I will have to completely rewrite my app as the SQL is stored in a String in java, and the app suplies the key index only at the end of the statement.

Answer 1

If you always consider only a single line in the process list (i.e. procsessListId = x), the following query should do a pretty simple and performant job:

select p.name from process p, list l
where l.id = 2
  and (p.id in (l.operation1, l.operation2, l.operation3))

Answer 2

One option is using union to unpivot the processlist table and joining it to the process table.

select p.name
from process p
join (select id,operation1 as operation from processlist
      union all
      select id,operation2  from processlist
      union all
      select id,operation3  from processlist
      --add more unions as needed based on the number of operations
      ) pl
on pl.operation=p.id
where pl.id = ?