Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Union query - place data into an imaginary field 1

Status
Not open for further replies.

DevelopV

Technical User
Mar 16, 2012
113
ZA
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
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
 
Found the solution!
Code:
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;
 
Doesn't this suffice ?
SQL:
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
 
How do I get SupplierProductId to show as long integer?
 
If SupplierProductId is defined as numeric in the tables, I'd replace "" with 0 (2 times).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top