TSQL Query Help Pt. III (Last)

I have 2 queries I got from the help from this site and they are:

SELECT gr.g_name, (DATEDIFF(d, r.res_checkout_date, r.res_checkin_date) * pp.rate ) + ISNULL(i.inv_amount, 0)
FROM guest_reservation gr LEFT OUTER JOIN invoice i ON gr.confirm_no = i.confirm_no
JOIN reservation r ON gr.confirm_no = r.confirm_no
JOIN price_plan pp ON r.price_plan = pp.price_plan;

SELECT g.g_name, DATEDIFF(d, r.res_checkin_date, r.res_checkout_date)*p.rate+coalesce(i.i开发者_如何学Gonv_amount, 0) as Amount
FROM reservation as r  INNER JOIN priceplan as p
ON r.price_plan = p.price_plan
INNER JOIN guest_reservation as g
ON r.confirm_no = g.confirm_no
LEFT OUTER JOIN invoice as i
ON r.confirm_no = i.confirm_no;

All the tables have the following data associated with them:

The guest reservation table has the following columns with data:

(confirm_no, agent_id, g_name, g_phone)

The reservation table has the following columns with data:

(confirm_no, credit_card_no, res_checkin_date, res_checkout_date, 
 default_villa_type, price_plan)

I need to somehow add items that a guest has ordered from the dining_order table (which is linked with the r_confirm_no from the dining_order table equaling the confirm_no from the reservation table), the items price must be taken from the dining_menu table (where dining_order.item equals dining_menu.item) and added into the above query.

The associated tables with the new information I need to add is:

The invoice table has the following columns with data:

(inv_no, inv_date, inv_amount, confirm_no).

The price plan table has the following columns with data:

(price_plan, rate, default_villa_type, bed_type)

This is untested but you could use a subquery to sum the dining items for each guest and add that to the total. This is also an outer join as the guest might not have made use of the dining facilities.

SELECT gr.g_name, (DATEDIFF(d, r.res_checkout_date, r.res_checkin_date) * pp.rate ) + ISNULL(i.inv_amount, 0) + ISNULL(d.total_dining, 0)
FROM guest_reservation gr 
LEFT OUTER JOIN invoice i ON gr.confirm_no = i.confirm_no
JOIN reservation r ON gr.confirm_no = r.confirm_no
JOIN price_plan pp ON r.price_plan = pp.price_plan
LEFT OUTER JOIN (SELECT r_confirmation_no, SUM(price) AS total_dining
                 FROM dining_order do JOIN dining_menu dm ON do.item = dm.item 
                 GROUP BY r_confirmation_no) AS d ON d.r_confirmation_no = r.confirm_no