By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

SELECT statement problem

100+
P: 228
Hi,

I have this 3 tables:
Expand|Select|Wrap|Line Numbers
  1. tblProducts: productid,producntname
  2. tblIngredient:ingredientid,ingredientname
  3. tblProductsIngredients:autoid,productcode,ingredientcode,amountneeded
  4.  
Products are made by certain amount of ingredients. I can add new products and all but am having problem wit updating product. During editing product, I want to list ingreidents that are not already added to the product along with ingredients (and their amounts) that are already specified for the product. I tried this code but it only returns already specified products while sometimes adds only one new ingredient to the list to be added:

Expand|Select|Wrap|Line Numbers
  1.  
  2. $strsql="SELECT * FROM viewexistingandnewingredientswithproducts WHERE productcode=$id OR productcode IS NULL";
  3.  
  4. }
  5.  
where viewexistingandnewingredientswithproducts is defined as

Expand|Select|Wrap|Line Numbers
  1.  
  2. select * from tblingredients,tblProductsIngredients where ingredientid=ingredientcode
  3.  
Aug 7 '12 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,347
What you've done is basically an inner join. It will never show ingredients that aren't in the product. What you want to do it outer join the tables.
Aug 7 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.