Mysql, SELECT according to the specialisation of the generic entity occurrence

Question

In my database I have a generic entity "user", such entity is specialized in two other "store" and "service_provider", this specialization has total and exclusive characteristic, so that, an occurrence of "user" must be associated with at least one specialised entity and only one "store" or "service_provider type".

The "type" field references to which specialization the occurrence of "User" belongs, type = '1'(store) and type='2'(service_provider).

Note that the table "store" has the field "company_name" that says which the company name and the table "service_provider" has the "name" referring to the physical person’s name.

DOUBT: in a query, it is possible to collect the values of an occurrence of "user", check which specialization this occurrence belongs (by the field "type"), and if it is a "store" give a JOIN with the field "company_name" or "name" if it is a "service_provider"?.

I thought that way (pseudo code):

 SELECIONAR LINHA DE user ONDE id_user = ?

 INNER JOIN

 IF ((SELECIONAR type DE user ONDE id_user = ? ) = '1',
    SELECIONAR company_name DE store ONDE id_user = ?,
    SELECIONAR name DE service_provider ONDE id_user = ?
 )

Answer 1

It is possible, and there are some approaches that may vary depending on your DBMS. The most comprehensive would be something like this:

SELECT
    user.id_user,
    user.type,
    (
        CASE user.type
            WHEN 1 THEN store.company_name
            WHEN 2 THEN service_provider.name
        END
    ) AS name
FROM
    user
    LEFT JOIN store ON user.id_user = store.id_user AND user.type = 1
    LEFT JOIN service_provider ON user.id_user = service_provider.id_user AND user.type = 2

Explaining:

We select all user records doing LEFT JOIN with both tables filtering by type, with this we will have the JOIN successfully for the record that exists. And SELECT we make a CASE to identify and return the table/field value according to type.