mysql - 在一个SQL中加入2个select语句

我在这里有2张桌子：

tblPurItem

-itemID

-retailRecipeID

tblPosRecipe

-recipeID

-recipeTypeID

retailRecipeID =配方ID

在这里，我有2个不同的select语句。现在，如何加入一个查询？下面，我正在尝试但仍无法正常工作的方式。

的SQL

SELECT A.itemID, A.categoryID, A.purchaseMeasurementID, A.stockMeasurementID,
A.calculationMeasurementID, A.itemName, A.itemDescription, A.itemPrice, A.lastPrice, A.averagePrice,
A.itemType, A.purchaseStatus, A.cutStock, A.chargeOut, A.retailItem, A.consignment,
A.marginConsignment, A.active,
A.retailRecipeID
, (SELECT B.itemCategoryName FROM tblPurItemCategory B WHERE A.categoryID = B.itemcategoryID LIMIT 1) AS
itemCategoryName
, (SELECT C.measurementName FROM tblPurMeasurement C WHERE A.purchaseMeasurementID= C.measurementID LIMIT 1) AS purchasemeasurementName
, (SELECT C.measurementName FROM tblPurMeasurement C WHERE A.stockMeasurementID= C.measurementID LIMIT 1) AS stockmeasurementName
, (SELECT C.measurementName FROM tblPurMeasurement C WHERE A.calculationMeasurementID= C.measurementID LIMIT 1) AS calculationmeasurementName
 FROM tblPurItem A

JOIN(SELECT D.recipeID, D.recipeTypeID, D.measurementID, D.defaultBarcode,
D.retailDefaultSellingPrice, E.posRecipeType, F.measurementName
FROM tblPosRecipe D
LEFT JOIN tblPosRecipeType E
ON D.recipeTypeID = E.posRecipeTypeID
LEFT JOIN tblPurMeasurement F
ON D.measurementID = F.measurementID
LEFT JOIN tblPosRecipe
ON A.retailRecipeID = D.recipeID)

最佳答案

这是您的查询。

SELECT A.itemID, A.categoryID, A.purchaseMeasurementID, A.stockMeasurementID,
    A.calculationMeasurementID, A.itemName, A.itemDescription, A.itemPrice, A.lastPrice, A.averagePrice,
    A.itemType, A.purchaseStatus, A.cutStock, A.chargeOut, A.retailItem, A.consignment,
    A.marginConsignment, A.active,
    A.retailRecipeID
    , (SELECT B.itemCategoryName FROM tblPurItemCategory B WHERE A.categoryID = B.itemcategoryID LIMIT 1) AS
    itemCategoryName
    , (SELECT C.measurementName FROM tblPurMeasurement C WHERE A.purchaseMeasurementID= C.measurementID LIMIT 1) AS purchasemeasurementName
    , (SELECT C.measurementName FROM tblPurMeasurement C WHERE A.stockMeasurementID= C.measurementID LIMIT 1) AS stockmeasurementName
    , (SELECT C.measurementName FROM tblPurMeasurement C WHERE A.calculationMeasurementID= C.measurementID LIMIT 1) AS  calculationmeasurementName
    , D.recipeID, D.recipeTypeID, D.measurementID, D.defaultBarcode, D.retailDefaultSellingPrice, E.posRecipeType, F.measurementName
FROM tblPurItem A
INNER JOIN tblPosRecipe D ON A.retailRecipeID = D.recipeID
LEFT JOIN tblPosRecipeType E ON D.recipeTypeID = E.posRecipeTypeID
LEFT JOIN tblPurMeasurement F ON D.measurementID = F.measurementID

关于mysql - 在一个SQL中加入2个select语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59083106/