Quote:
Originally Posted by iggyntangs
To this I need to add what a guest may have ordered (food wise into their total)
So we have a dining_order table with the following columns with data:
(r_confirmation_no, item)
We have a dining_menu table with the following columns with data:
(item, price, description)
|
If I interpret the data model correctly, you have many food items to sum up, and add to their total bill. To do that, join dining order and dining menu together so you have prices with each item they've ordered. Then group by r_confirmation_no and sum price. This will return a single row per r_confirmation_number with a total dining price. Take all of that and use it as a subquery joined to your main view, and add the dining price to the total.
Using subqueries is the least cluttered way to organise your database. Another method which is easier to follow when learning (but creates clutter) is to create views that you then join to the main view you are building. So you could create a total_dining_price view using the GROUP BY described above. Then add that view to your main view with a join, just like you would with any table.