I have a correct subquery that returns results but when I use it as an "IN" clause I get error 1064
You can't SELECT like this, where you dynamically SELECT the table name. That's why it's not working. The subselect works, but.. try to replace subselect by a constant (i.e. known) name and it still won't work.
For something like this you either need an if then else flow or dynamic SQL or do it on application side.