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

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 

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