Union query - place data into an imaginary field 1

[DevelopV]

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

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

 

[DevelopV]

Found the solution!

SELECT tblRecipeIngredients.RecipeId,  tblRecipeIngredients.SupplierProductId, "" as OwnRecipeId,  tblRecipeIngredients.Quantity,  tblRecipeIngredients.DenominationId from tblRecipeIngredients UNION SELECT tblRecipeOwnIngredients.RecipeId, "" AS SupplierProductId, tblRecipeOwnIngredients.SupplierProductId AS OwnRecipeId, tblRecipeOwnIngredients.Quantity, tblRecipeOwnIngredients.DenominationId FROM tblRecipeOwnIngredients;

 

[PHV]

Doesn't this suffice ?

SELECT RecipeId, SupplierProductId, "" AS OwnRecipeId, Quantity, DenominationId FROM tblRecipeIngredients 
UNION SELECT RecipeId, "", SupplierProductId, Quantity, DenominationId FROM tblRecipeOwnIngredients

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DevelopV]

Thanks PHV!

 

[DevelopV]

How do I get SupplierProductId to show as long integer?

 

[PHV]

If SupplierProductId is defined as numeric in the tables, I'd replace "" with 0 (2 times).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886