Adamhumbug Posted December 11, 2019 Share Posted December 11, 2019 Hi all, i am so sorry at how bad i am at setting the title my posts. Before we get to my question, i would really appreciate it if someone could let me know how you create a table on here to show results. The following sql selects all of the items in a menu and lists them out which is great. SELECT menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id FROM ssm_menu a INNER JOIN ssm_menu_connection b on a.menu_id = b.menu_id INNER JOIN ssm_menu_items c on b.menu_item_id = c.menu_item_id WHERE a.menu_id = 1 menu_name menu_price menu_item_name menu_item_category menu_item_id menu_id Menu One 22.90 Bruschetta of Italian prosciutto, Fior di latte mo... Starter 1 1 Menu One 22.90 Crisp skin chicken breast, stuffed with sage, shal... Main 7 1 Menu One 22.90 Tagine of vegetable lightly spiced, on preserved f... Main 15 1 Menu One 22.90 Test Desert Desert 23 1 The above is the full menu and shows all menu items within the menu. When they have already made some selections, and submitted a menu, i want to display the quantities next to the name of the item. I have the following sql which is just an amendment of the above SELECT menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty FROM ssm_menu a INNER JOIN ssm_menu_connection b on a.menu_id = b.menu_id INNER JOIN ssm_menu_items c on b.menu_item_id = c.menu_item_id inner join ssm_menu_order d on c.menu_item_id = d.menu_item_id WHERE a.menu_id = 1 menu_name menu_price menu_item_name menu_item_category menu_item_id menu_id job_id menu_item_qty Menu One 22.90 Bruschetta of Italian prosciutto, Fior di latte mo... Starter 1 1 26 111 Menu One 22.90 Bruschetta of Italian prosciutto, Fior di latte mo... Starter 1 1 27 1 Menu One 22.90 Crisp skin chicken breast, stuffed with sage, shal... Main 7 1 26 222 Menu One 22.90 Tagine of vegetable lightly spiced, on preserved f... Main 15 1 26 333 Menu One 22.90 Test Desert Desert 23 1 26 444 The issue that i have here is that it is displaying some items twice because they have been selected by another user. I would like to show the whole menu but only show the qty's that relate to job_id 27. I hope this makes sense and if further structure is required, i will be happy to provide it. Kind Regards Adam Quote Link to comment https://forums.phpfreaks.com/topic/309669-only-include-db-fields-when-another-field-x/ Share on other sites More sharing options...
kicken Posted December 11, 2019 Share Posted December 11, 2019 Make your join a LEFT JOIN and add the job id condition as part of the join. SELECT menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty FROM ssm_menu a INNER JOIN ssm_menu_connection b ON a.menu_id = b.menu_id INNER JOIN ssm_menu_items c ON b.menu_item_id = c.menu_item_id LEFT JOIN ssm_menu_order d ON c.menu_item_id = d.menu_item_id AND d.job_id=27 WHERE a.menu_id = 1 On a side note, consider re-formatting your queries. Your current style is quite difficult to read, something like what I put above is much easier. 1 Quote Link to comment https://forums.phpfreaks.com/topic/309669-only-include-db-fields-when-another-field-x/#findComment-1572435 Share on other sites More sharing options...
Adamhumbug Posted December 11, 2019 Author Share Posted December 11, 2019 1 hour ago, kicken said: Make your join a LEFT JOIN and add the job id condition as part of the join. SELECT menu_name, menu_price, menu_item_name, menu_item_category, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty FROM ssm_menu a INNER JOIN ssm_menu_connection b ON a.menu_id = b.menu_id INNER JOIN ssm_menu_items c ON b.menu_item_id = c.menu_item_id LEFT JOIN ssm_menu_order d ON c.menu_item_id = d.menu_item_id AND d.job_id=27 WHERE a.menu_id = 1 On a side note, consider re-formatting your queries. Your current style is quite difficult to read, something like what I put above is much easier. Perfect, thanks you so much for this - i was banging my head with it. I will for sure have a rethink about my formatting. Thanks Again Quote Link to comment https://forums.phpfreaks.com/topic/309669-only-include-db-fields-when-another-field-x/#findComment-1572436 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.