I have two tables:
tblRecipeIngredients
fields: RecipeId, SupplierProductId, Quantity, DenominationId
tblRecipeOwnIngredients
fields: RecipeId, SupplierProductId, Quantity, DenominationId
I want to create a union query that includes the "imaginary" field "OwnRecipeId"
Records from the table tblRecipeIngredients must return null in the "imaginary" field "OwnRecipeId"
Records from the table tblRecipeOwnIngredients must return null in the field "SupplierProductId" and the "imaginary" field "OwnRecipeId" must contain the data from field "SupplierProductId"
I have created the union query
tblRecipeIngredients
fields: RecipeId, SupplierProductId, Quantity, DenominationId
tblRecipeOwnIngredients
fields: RecipeId, SupplierProductId, Quantity, DenominationId
I want to create a union query that includes the "imaginary" field "OwnRecipeId"
Records from the table tblRecipeIngredients must return null in the "imaginary" field "OwnRecipeId"
Records from the table tblRecipeOwnIngredients must return null in the field "SupplierProductId" and the "imaginary" field "OwnRecipeId" must contain the data from field "SupplierProductId"
I have created the union query
Code:
SELECT RecipeId, SupplierProductId, "" as OwnRecipeId, Quantity, DenominationId from tblRecipeIngredients UNION SELECT RecipeId, "" AS SupplierProductId, SupplierProductId AS OwnRecipeId, Quantity, DenominationId FROM tblRecipeOwnIngredients;[code]
for all the records returned from "tblRecipeOwnIngredients":
* SupplierProductId is returned as null, which is correct
* SupplierProductId AS OwnRecipeId is also returned as null, which is incorrect. It should reflect the value SupplierProductId in table tblRecipeOwnIngredients
How do I modify the query?
Thanks in advance