dyluck Posted January 11, 2010 Share Posted January 11, 2010 Hi There. How do i do a join query or some sort of query that only gives me results based on the fact that a certian value doesn't exist in another table only given the ID number of the first table? Example: Types of Fruits 1Apple 2Orange 3Banana 4Kiwi Fruits In Stock 1Apple 2Apple 3Banana 4Banana 5Banana 6Banana How can I do a query here where the result would be: Orange and Kiwi (as they doesn't appear in the second table) Thanks for your help! Quote Link to comment https://forums.phpfreaks.com/topic/188004-join-query/ Share on other sites More sharing options...
kickstart Posted January 11, 2010 Share Posted January 11, 2010 Hi Something like this SELECT TypeOfFruit FROM TypesOfFruitTable LEFT OUTER JOIN FruitsInStockTable ON TypeOfFruit.Fruit = FruitsInStockTable.Fruit WHERE FruitsInStockTable.Fruit IS NULL All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/188004-join-query/#findComment-992620 Share on other sites More sharing options...
dyluck Posted January 11, 2010 Author Share Posted January 11, 2010 Thanks very much Keith. When I try to run the query in my code, it doesn't show the rest of the page... I guess this was 1 step further. I didn't write my question properly the first time sorry what if there was a table that matched a type first TypesOfFood 1 MEAT 2 FRUIT 3 VEGITABLES AvailableFoods 1 2 Apple 2 2 Orange 3 2 Banana 4 2 Kiwi 5 3 Brocoli 6 3 Carrot 7 1 T-Bone Steak FoodsInStock 1 Apple 2 Apple 3 Banana 4 Banana 5 Banana 6 Banana Given the ability to match #2 from TypesOfFood (Fruit) Can I do the following? SELECT * FROM AvailableFoods LEFT OUTER JOIN FoodsInStockTable ON TypesOfFood.Name = FoodsInStock.Name WHERE TypesOfFood.TypeID = '2' AND FoodsInStock.Name IS NULL Quote Link to comment https://forums.phpfreaks.com/topic/188004-join-query/#findComment-992736 Share on other sites More sharing options...
kickstart Posted January 11, 2010 Share Posted January 11, 2010 Hi I think what you are trying to do is:- SELECT * FROM TypesOfFood JOIN AvailableFoods ON TypesOfFood.TypeID = AvailableFoods.TypeID LEFT OUTER JOIN FoodsInStockTable ON TypesOfFood.Name = FoodsInStock.Name WHERE TypesOfFood.TypeName = 'FRUIT' AND FoodsInStock.Name IS NULL If you know the TypeId then no need to use the TypesOfFood table:- SELECT * FROM AvailableFoods LEFT OUTER JOIN FoodsInStockTable ON TypesOfFood.Name = FoodsInStock.Name WHERE AvailableFoods.TypeId = '2' AND FoodsInStock.Name IS NULL Probably be better to have a FoodId rather than storing the food name in both the AvailableFoods and FoodsInStockTable tables. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/188004-join-query/#findComment-992778 Share on other sites More sharing options...
aebstract Posted January 11, 2010 Share Posted January 11, 2010 TypesOfFood ID Name 1 MEAT 2 FRUIT 3 VEGITABLES AvailableFoods ID TypeID Name 1 2 Apple 2 2 Orange 3 2 Banana 4 2 Kiwi 5 3 Brocoli 6 3 Carrot 7 1 T-Bone Steak FoodsInStock ID Name 1 Apple 2 Apple 3 Banana 4 Banana 5 Banana 6 Banana SELECT * FROM AvailableFoods LEFT OUTER JOIN FoodsInStockTable ON AvailableFoods.Name = FoodsInStock.Name WHERE AvailableFoods.TypeID = '2' AND FoodsInStock.Name IS NULL Not sure what you're doing with the null part, but this will do it correctly. If I understand what you're trying to do anyway. Quote Link to comment https://forums.phpfreaks.com/topic/188004-join-query/#findComment-992781 Share on other sites More sharing options...
kickstart Posted January 11, 2010 Share Posted January 11, 2010 Not sure what you're doing with the null part, but this will do it correctly. The NULL check is there to exclude foods that are both on available foods and on foods in stock. I presume he needs a list of items to order. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/188004-join-query/#findComment-992797 Share on other sites More sharing options...
dyluck Posted January 11, 2010 Author Share Posted January 11, 2010 Thanks kickstart and aebstract Turns out this code worked for me: SELECT *FROM AvailableFoodsLEFT OUTER JOIN FoodsInStockTableON AvailableFoods.Name = FoodsInStock.NameWHERE AvailableFoods.TypeID = '2' AND FoodsInStock.Name IS NULL Its pretty cool what you can do with MySQL! Quote Link to comment https://forums.phpfreaks.com/topic/188004-join-query/#findComment-993114 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.