MySQL Forums
Forum List  »  Newbie

Join 3 tables?
Posted by: John Blake
Date: April 21, 2018 09:46AM

I am trying to figure out how many times each family stayed at a resort and need to compute the total revenue collected from each family. I have to include the GNum, LastName, number of stays and total revenue.

I have narrowed it down to 3 tables (I think).

Table 1: Con
- ConNum
BldNum
UnNum
WeekFee

Table 2: ConS
- ConNum
- GNum
StartDay
EndDay

Table 3: Guest
- GNum
LastName

I have it so that I can get the number of times they have stayed, but I can not figure out how to include the total revenue in the calculation. I am thinking maybe I have to combine 3 tables or do a sub-query of some sort? Maybe WeeklyFee * the number I come up with as number of stays? Each time the guest is there is counted as a weekly stay which is why I only used end date.

Here is what I have so far:
SELECT
ConS.GNum, guest.LastName, count(ConS.EndDay) AS 'How Many Stays'
FROM
ConS
JOIN
guest using(GNum)
GROUP BY
guest.GNum

Options: ReplyQuote


Subject
Written By
Posted
Join 3 tables?
April 21, 2018 09:46AM
April 21, 2018 10:48AM
April 21, 2018 12:31PM
April 21, 2018 03:51PM
April 21, 2018 06:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.