CODEWITHSUNDEEP
sqldatabasems-access
user995387
user995387

Reputation: 385

How to query multiple database in access for my condition

Here are my conditions.

I have three tables with following columns

Recipe 
   - rID
   - pID
   - ingID
   - ingAmount

Product
   -pID
   -pName

Ingredient
   - ingID
   - ingName

for Recpe Table, Recipe.ingID, Recipe.pID is referenced from Product's Product.pID (Primary Key), While the same ingredient.ingID is also referenced from Product.ingID.

In general,

Recipe.pID = Product.pID
Recipe.ingID = Product.pID
Recipe.ingID = Ingredient.ingID

I want to retrieve following columns using just single query in ACCESS.

pID | pName | ingID | ingName | ingAmount |

I tried following:

SELECT Recipe.pID, Product.pName, Recipe.ingID, 
       Ingredient.ingName, Recipe.ingAmount 
  FROM Recipe, Product, Ingredient 
 WHERE Recipe.pID = 5 
       AND (
            Recipe.ingID = Ingredient.ingID 
            OR Recipe.ingID = Product.pID
           );

The problem is, (Recipe.ingID = Ingredient.ingID OR Recipe.ingID = Product.pID) part gets evaluated first hence multiple rows are queried.

If you got what I wanted to ask please help me.

Upvotes: 1

Views: 253

Answers (1)

onedaywhen
onedaywhen

Reputation: 57023

SELECT Recipe.pID, Recipe.ingID, Recipe.ingAmount, 
       Ingredient.ingName AS element_name, 
       'Ingredient' AS element_type
  FROM Recipe INNER JOIN Ingredient
          ON Recipe.ingID = Ingredient.ingID 
 WHERE Recipe.pID = 5 
UNION
SELECT Recipe.pID, Recipe.ingID, Recipe.ingAmount, 
       Product.pName AS element_name, 
       'Product' AS element_type
  FROM Recipe INNER JOIN Product
          ON Recipe.pID = Product.pID
 WHERE Recipe.pID = 5
UNION
SELECT Recipe.pID, Recipe.ingID, Recipe.ingAmount, 
       Product.pName AS element_name, 
       'Product as Ingredient' AS element_type
  FROM Recipe INNER JOIN Product
          ON Recipe.ingID = Product.pID
 WHERE Recipe.pID = 5;

Upvotes: 1

Related Questions