SQL how to make a query(s) in this situation?

Question

SQL

There is a table of goods objects (sports store). It contains the main fields common to all types of goods. There are several tables-types of objects (shirts, shoes, ... sports pit) that are "inherited" from the main table. A relationship between an object in the underlying table and a one-to-one table type. In the main table there is a field "type"

$q = '
  SELECT
    *
  FROM
    catalog_product prod
      INNER JOIN
    catalog_product_type_shirt prod_type
  ON
    prod.id=prod_type.product_id';

In this query, the selection comes from a KNOWN table-type.
a) When selecting by id, we do not know in advance what type this object belongs to
. b) There can be goods of different types in one category. So, again, when making a request for a selection by category, we do not know which tables to select data from.
Question: in order to select an object (s) is it necessary to make TWO queries to the database? Those. one to select type in the main table. And the second, we compile on the basis of the received data and then send it.
Or can it be done in ONE request?
*DBMS - MySQL
**If not, is it possible to do it "dirty ways", just for the sake of curiosity =)

Answer 1

It depends on what to consider in dirty ways. Some out there rewrite engines for the sake of performance, or even develop their own DBMS.
And so it is possible through IF and subqueries, for example.
stackoverflow.com/questions/17723808/mysql-if-stat...