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:
x
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?
Advertisement
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;