how to get multiple columns from subquery inseide sql statement mysql

Question

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?

Please provide sample data, desired results, and a clear explanation of what you want to do. — Gordon Linoff
– Gordon Linoff, Commented Apr 27, 2021 at 10:37

nachospiu · Accepted Answer · 2021-04-27 11:18:29Z

1

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;

edited Apr 27, 2021 at 11:18

answered Apr 27, 2021 at 11:12

nachospiu

2,0792 gold badges10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nachospiu Over a year ago

You are welcome @Hamada, I am glad I helped you.

Ranjeet Singh · Accepted Answer · 2021-04-27 11:25:11Z

0

Hi join can be used for the table used in the sub query a sample query can the following - SELECT tblacc.AccID AS xAccID, tblacc.AccName AS xAccName, tblprod.ProductID AS xProdID, tblprod.ProductName AS xProdName, tblprodprices.PriceID AS xPriceID, tblprodprices.PriceValue AS xPriceValue, tblprodprices.PricePallet AS xPalletPrice FROM tblaccounts tblacc, tblproducts tblprod, tblproductsprices tblprodprices WHERE tblacc.AccCat = 'agent' and tblprodprices.PriceProductID = tblprod.ProductID AND tblprodprices.PriceResoID = tblacc.AccID ORDER BY tblacc.AccName, tblprod.ProductName

answered Apr 27, 2021 at 11:25

Ranjeet Singh

616 bronze badges

Collectives™ on Stack Overflow

how to get multiple columns from subquery inseide sql statement mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related