Get records from multiple tables from a single query

Get records from multiple tables from a single query.

I have the following structure and data, there are 4 tables:

Table: Master_Product

Prod_Code   perfil  Marca   Orden   Catengoria
1           1       10      25      2
2           1       10      26      2
3           1       10      27      2
4           1       10      28      6
5           1       10      29      6
6           1       10      30      6
7           1       10      31      6

Table: Product_Structure

Prod_Code   perfil  UxC CxP
1           1       4   60
2           1       10  60
3           1       8   60
4           1       12  60
5           1       20  60
6           1       24  60
7           1       50  60

Table: Product_Supplier

Prod_Code   perfil  Proveedor
1           1       30
2           1       30
3           1       30
4           1       30
5           1       40
6           1       40
7           1       40

Table: Product_Price

Prod_Code   perfil  Price  Prod_DateUpd
1           1       10     20/10/2021
2           1       10     20/10/2021
1           1       5      19/10/2021
2           1       5      19/10/2021
3           1       20     20/10/2021
1           1       20     15/10/2021
1           1       15     15/10/2021

these tables have a Manual relationship by product code and profile: then the previous programmer developed a script that executes 4 queries individually to extract the data:

$stmtpre1 = "SELECT * FROM Master_Producto WHERE Prod_Code='$Prod_Code' AND perfil=$Prod_FT;";
$stmtpre2 = "SELECT * FROM Producto_Estructura WHERE Prod_Code='$Prod_Code' AND perfil=$Prod_FT;";
$stmtpre3 = "SELECT * FROM Producto_Proveedor WHERE Prod_Code='$Prod_Code' AND perfil=$Prod_FT;";
$stmtpre4 = "SELECT * FROM Producto_Precio WHERE Prod_Code='$Prod_Code' AND perfil=$Prod_FT;";

It then executes the queries, associates them and returns a single array:

$result = [];
$data1                  = $this->BDquery($stmtpre1, DB_PRODUCT);
$result['Prod_Profile'] = $this->BDasociar($data1);
$data2                  = $this->BDquery($stmtpre2, DB_PRODUCT);
$result['Prod_Info']    = $this->BDasociar($data2);
$data3                  = $this->BDquery($stmtpre3, DB_PRODUCT);
$result['Prod_Prov']    = $this->BDasociar($data3);
$data4                  = $this->BDquery($stmtpre4, DB_PRODUCT);
$result['Prod_Precio']  = $this->BDasociar($data4);

return $result;

I have come to the conclusion that this implementation is not efficient, and that the multiple connections and analysis in queries when handling more than 2000 products will drastically penalize the execution time, I have thought about rethinking the script and refactoring it, but I do not know how to implement it UNION of tables.

rent collaborates with me with the refactoring of the Query, the ideal would be to execute only one.

$stmtpre = implode(" UNION ", [$stmtpre1,$stmtpre2,$stmtpre2,$stmtpre2]);

But it's not working.

UPDATE #1

Example:

(SELECT * FROM Master_Producto WHERE Prod_Code='1' AND perfil=1) UNION (SELECT * FROM Producto_Estructura WHERE Prod_Code='1' AND perfil=1) UNION (SELECT * FROM Producto_Proveedor WHERE Prod_Code='1' AND perfil=1) UNION (SELECT * FROM Producto_Precio WHERE Prod_Code='1' AND perfil=1) 

Mistake:

The SELECT command used has different number of columns

UPDATE #2 Another Attempt using Right Join: note: it is contemplated to fetch only the last record ofProducto_Precio

SELECT
    *
FROM
    Master_Producto
RIGHT JOIN(
        Producto_Estructura
    ON
        Master_Producto.Prod_Code = Producto_Estructura.Prod_Code AND Master_Producto.Prod_PF = Producto_Estructura.Prod_PF
    )
RIGHT JOIN(
        Producto_Proveedor
    ON
        Producto_Estructura.Prod_Code = Producto_Proveedor.Prod_Code AND Producto_Estructura.Prod_PF = Producto_Proveedor.Prod_PF
    )
RIGHT JOIN(
        Producto_Precio
    ON
        Producto_Estructura.Prod_Code = Producto_Precio.Prod_Code AND Producto_Estructura.Prod_PF = Producto_Precio.Prod_PF
    ORDER BY
        Prod_DateUpd
    DESC
LIMIT 1
    )

Mistake:

Something is wrong in your syntax near 'ON

Is there another alternative?: yes.