how to get multiple columns from subquery inseide sql statement mysql

actually, I found many solutions for my issue but I didn’t understand how to apply to my issue!

my issue is I have the following SQL statement:

SELECT
                                `tblacc`.`AccID` AS xAccID,
                                `tblacc`.`AccName` AS xAccName,
                                `tblprod`.`ProductID` AS xProdID,
                                `tblprod`.`ProductName` AS xProdName,
                                (
                                SELECT
                                    tblproductsprices.PriceID
                                FROM
                                    tblproductsprices
                                WHERE
                                    tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
                            ) AS xPriceID,
                            (
                            SELECT
                                tblproductsprices.PriceValue
                            FROM
                                tblproductsprices
                            WHERE
                                tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
                            ) AS xPriceValue,
                            (
                            SELECT
                                tblproductsprices.PricePallet
                            FROM
                                tblproductsprices
                            WHERE
                                tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND tblproductsprices.PriceResoID = `tblacc`.`AccID`
                            ) AS xPalletPrice
                            FROM
                                tblaccounts tblacc,
                                tblproducts tblprod
                            WHERE
                                tblacc.`AccCat` = 'agent'
                            ORDER BY
                                tblacc.AccName,
                                tblprod.ProductName

what I need is to replace the following part in one part:

(
                                SELECT
                                    tblproductsprices.PriceID
                                FROM
                                    tblproductsprices
                                WHERE
                                    tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
                            ) AS xPriceID,
                            (
                            SELECT
                                tblproductsprices.PriceValue
                            FROM
                                tblproductsprices
                            WHERE
                                tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
                            ) AS xPriceValue,
                            (
                            SELECT
                                tblproductsprices.PricePallet
                            FROM
                                tblproductsprices
                            WHERE
                                tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND tblproductsprices.PriceResoID = `tblacc`.`AccID`
                            ) AS xPalletPrice

to get the all columns in one subquery with the same conditions?

Answer

I don’t know your data schema. Do you try making a CROSS JOIN (first) and a LEFT JOIN with table tblproductsprices?

SELECT
    `tblacc`.`AccID` AS xAccID,
    `tblacc`.`AccName` AS xAccName,
    `tblprod`.`ProductID` AS xProdID,
    `tblprod`.`ProductName` AS xProdName,
    tblproductsprices.PriceID AS xPriceID,
    tblproductsprices.PriceValue AS xPriceValue,
    tblproductsprices.PricePallet AS xPalletPrice
    
    FROM tblaccounts tblacc

    CROSS JOIN tblproducts tblprod
    
    LEFT JOIN tblproductsprices ON tblproductsprices.PriceProductID = `tblprod`.`ProductID` AND `tblproductsprices`.`PriceResoID` = `tblacc`.`AccID`
    
    WHERE tblacc.`AccCat` = 'agent'

    ORDER BY tblacc.AccName, tblprod.ProductName;