Compute table name to join from row value

I have tables that look somewhat like this:

Table name: products

ID      PRODUCT_ID      TYPE       PRICE      ...
1       111             computer   xxx
2       222             book       xx
3       333             computer   xxxx

----------------------------------------------------------------

Table name: products_computer

ID      PRODUCT_ID      CPU     RAM      ....
1       111             amd     16
2       333             intel   8

----------------------------------------------------------------

Table name: products_book

ID      PRODUCT_ID      AUTHOR          YEAR_PUBLISHED        ....
1       222             Stephen King    xxxx

As you can see, in table products every single product is listed with columns of what they have in common ( like the price ), and the other tables contain specific information for that product type.

Now lets say we want to list every product independend of their type ordered by the price, and also include the specific information of the products_* tables in the query, to not manually make a second query just to retrieve that information.

While I hardly ever use databases and SQL, my inexperience ran into various pitfalls trying to concat strings to build the products_* table name for a JOIN or make a "middle table" that maps the procucts.TYPE value to the actual table name, where I found later in the documentation that it simply doesn't work trying to "bind" a column/row value as the table name.

Is there any "trick" to make this work ( having everything in 1 query ), or do I really have to make a second query manually in my code ?