How to query multiple database in access for my condition

Question

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.

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.

The condition should be Recipe.ingID = Ingredient.ingID AND Recipe.pID = Product.pID — Florin Ghita
– Florin Ghita, Commented Jan 20, 2012 at 12:53
@Florin: Nope! I want Recipe.ingID = Ingredient.ingID equals to true or Recipe.ingID = product.pID equals to true — user995387
– user995387, Commented Jan 20, 2012 at 16:19
@user995387 When you say "multiple rows are queried", what is the problem, are you getting duplicate records, if so on which field/fields? — Matt Donnan
– Matt Donnan, Commented Jan 20, 2012 at 17:50

onedaywhen · Accepted Answer · 2012-01-20 13:43:31Z

1

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;

answered Jan 20, 2012 at 13:43

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query multiple database in access for my condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related