Save other ID in database

I have 2 table here :

 1. tblPurItem : 

    itemID, categoryID, purchaseMeasurementID, stockMeasurementID, calculationMeasurementID, retailrecipeID

 2. tblRecipe :

    recipeID, recipeName, recipeType

*retailRecipeID = recipeID

​x
 
 1. tblPurItem : ​    itemID, categoryID, purchaseMeasurementID, stockMeasurementID, calculationMeasurementID, retailrecipeID​ 2. tblRecipe :​    recipeID, recipeName, recipeType​*retailRecipeID = recipeID​

How do I get the value retailRecipeID in tblPurItem? Because if I add like (SELECT D.recipeName FROM tblPosRecipe D WHERE A.retailRecipeID = D.recipeID LIMIT 1) AS recipeName , recipeName is null which is there is no data. I have to get the retailRecipeID because in this situation, I have form. Form 1 is tblPurItem and form 2 is tblRecipe. The form 1 have no problem but form 2 have problem.

When I success to submit the form 1, the form 2 will be popup on radiobutton= yes so that form 2 success to submit data. Now, my problem is data form 2 will be lost because do not link with form 1. And retailRecipeID also null in database. Anyone know the solution? Thank you!

It should be when I open back the form 1 and form 2 the data will displayed back but it show blank not the previous data.

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
        , (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

 
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        , (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 ​

Answer

use left join for tblRecipe and coalesce() function to get value 0 in your presentation layer in case of no matching recipe

select
    coalesce(t2.recipeID, 0) as recipeID
    , t1.itemID
    , t1.categoryID
    , t1.purchaseMeasurementID
    , t1.stockMeasurementID
    , t1.calculationMeasurementID
    , t1.itemName
    , t1.itemDescription
    , t1.itemPrice
    , t1.lastPrice
    , t1.averagePrice
    , t1.itemType
    , t1.purchaseStatus
    , t1.cutStock
    , t1.chargeOut
    , t1.retailItem
    , t1.consignment
    , t1.marginConsignment
    , t1.active
    , (SELECT B.itemCategoryName FROM tblPurItemCategory B WHERE t1.categoryID = B.itemcategoryID LIMIT 1) AS itemCategoryName
    , (SELECT C.measurementName FROM tblPurMeasurement C WHERE t1.purchaseMeasurementID= C.measurementID LIMIT 1) AS purchasemeasurementName
    , (SELECT C.measurementName FROM tblPurMeasurement C WHERE t1.stockMeasurementID= C.measurementID LIMIT 1) AS stockmeasurementName
    , (SELECT C.measurementName FROM tblPurMeasurement C WHERE t1.calculationMeasurementID= C.measurementID LIMIT 1) AS calculationmeasurementName
from tblPurItem t1
left join tblRecipe t2 on t1.retailrecipeID = t2.recipeID

 
select    coalesce(t2.recipeID, 0) as recipeID    , t1.itemID    , t1.categoryID    , t1.purchaseMeasurementID    , t1.stockMeasurementID    , t1.calculationMeasurementID    , t1.itemName    , t1.itemDescription    , t1.itemPrice    , t1.lastPrice    , t1.averagePrice    , t1.itemType    , t1.purchaseStatus    , t1.cutStock    , t1.chargeOut    , t1.retailItem    , t1.consignment    , t1.marginConsignment    , t1.active    , (SELECT B.itemCategoryName FROM tblPurItemCategory B WHERE t1.categoryID = B.itemcategoryID LIMIT 1) AS itemCategoryName    , (SELECT C.measurementName FROM tblPurMeasurement C WHERE t1.purchaseMeasurementID= C.measurementID LIMIT 1) AS purchasemeasurementName    , (SELECT C.measurementName FROM tblPurMeasurement C WHERE t1.stockMeasurementID= C.measurementID LIMIT 1) AS stockmeasurementName    , (SELECT C.measurementName FROM tblPurMeasurement C WHERE t1.calculationMeasurementID= C.measurementID LIMIT 1) AS calculationmeasurementNamefrom tblPurItem t1left join tblRecipe t2 on t1.retailrecipeID = t2.recipeID​

Advertisement

Answer