May 10, 2004 at 1:28 pm
I have the following 3 tables:
TABLE: Person
PersonID, PersonName
TABLE: Apartment
ApartmentID, PersonID, ApartmentNo
TABLE: Rent
RentID, ApartmentID, DateOfRentTakingEffect, RentAmount
I'd like to select all the apartments that a particular person lived in but only display the highest rent that they paid. So far I've got...
SELECT p.PersonName, a.ApartmentNo, r.DateOfRentTakingEffect, r.RentAmount
FROM Person p
JOIN Apartment a
ON p.PersonID = a.PersonID
JOIN Rent r
ON a.apartmentID = r.apartmentID
WHERE p.PersonID = 1
The thing is that I'm getting back too many results when there are multiple rents for an apartment. I'm not sure how to restrict this query to show only the highest rent that was paid.
May 10, 2004 at 2:38 pm
Instead of selecting all the results from Rent you need to select the MAX Rent so add a Select Statment in place of the Table Rent and use a Group By Clause with having in place of the where.
SELECT p.PersonName, a.ApartmentNo, r.DateOfRentTakingEffect, r.RentAmount
FROM Person p
JOIN Apartment a
ON p.PersonID = a.PersonID
JOIN (Rent) r
ON a.apartmentID = r.apartmentID
GROUP BY a.apartmentID
HAVING p.PersonID = 1
Regards,
Matt
May 10, 2004 at 6:50 pm
I'm still having no success with this. I'm going to list everything here in case someone is kind enough to take a look at this. I can seriously use the help. Basically, I'm trying to select the amount of rent paid for an apartment on the date of the most recent lease. I'm making that the assumption that each lease is for 1 year only. As an example, let's assume that John Smith lived in the same building for 4 years. The first 2 years he lived in apartment "2B" and the next 2 years he lived in apartment "3C". This means he signed a total of 4 leases.
In my example (below) he signed lease 1 on 1/1/2000 (for apartment "2B") and paid $1250. He signed lease 2 on 1/1/2001 (also for apartment "2B") and paid $950 (for some reason they significantly lowered his rent). He signed lease 3 on 1/1/2002 (for apartment "3C") and paid $1450. He signed lease 4 on 1/1/2003 (again for apartment "3C") and paid $1700 (a pretty big raise in his rent).
I'm trying to write a query that will figure out the amount of money he paid on his most recent lease for each apartment. So, in my example, apartment 2B would return $950 and apartment 3C would return $1700. I wrote the query (below) but it has an error that I can't resolve. Below are the tables (with the actual data) as well as the query I wrote and its output...
TABLE: Person
PersonID: 1
PersonName: "John Smith"
TABLE: Apartment
ApartmentID: 1,2
PersonID: 1,1
ApartmentNo: "2B", "3C"
TABLE: Rent
RentID: 1,2,3,4
ApartmentID 1,1,2,2
DateOfRentTakingEffect: "1/1/2000", "1/1/2001", "1/1/2002", "1/1/2003"
RentAmount: 1250, 950, 1450, 1700
MY QUERY IS:
SELECT p.PersonID, p.PersonName, a.ApartmentNo as "AptNo", max(r.RentAmount) as "MostRecentRent", max(r.DateOfRentTakingEffect) as "MostRecentLeaseDate"
FROM Person p
JOIN Apartment a
ON p.PersonID = a.PersonID
JOIN Rent r
ON a.ApartmentID = r.ApartmentID
GROUP BY r.ApartmentID, a.ApartmentNo, p.PersonID, p.PersonName
HAVING p.PersonID = "1"
THE RESULTS OF MY QUERY ARE:
PersonID: 1,1
PersonName: "John Smith, "John Smith"
AptNo: "2B", "3C"
MostRecentRent: 1250, 1700
MostRecentLeaseDate: 1/1/2001, 1/1/2003
Everything is correct except the Rent for the 1st returned row. I know the cause of it (I'm using the "max" function) but I can't figure out a solution. Basically I'd like to somehow tie the "DateOfRentTakingEffect" to the "RentAmount" but I don't know how to do it.
May 10, 2004 at 8:37 pm
You got the results that you asked for. Max Date and Max Amount.
If all you want is the Amount on the Max Date then remove the Max criteria from the Amount field.
Regards,
Matt
May 10, 2004 at 9:47 pm
My last post was incorrect, as was my first since the group by needed to be done in an Exists clause. This should give you what you need.
SELECT p.PersonID, p.PersonName, a.ApartmentNo, r.RentAmount as "Most Recent Rent", r.DateOfRentTakingEffect
FROM Person p
INNER JOIN Apartment a
ON p.PersonID = a.PersonID
INNER JOIN Rent r
ON a.ApartmentID = r.ApartmentID
GROUP BY p.PersonID, p.PersonName, a.ApartmentNo, r.RentAmount, r.DateOfRentTakingEffect
HAVING p.PersonID = 1
AND EXISTS
(SELECT rr.ApartmentID, max(rr.DateOfRentTakingEffect) as RentDate
FROM RENT rr
GROUP BY rr.ApartmentID
HAVING max(rr.DateOfRentTakingEffect) = r.DateOfRentTakingEffect)
Regards,
Matt
May 11, 2004 at 3:15 am
Another solution that may be a little better (in terms of performance) would be:
SELECT p.PersonID, p.PersonName, a.ApartmentNo, r1.RentAmount as LatestRent, r1.DateOfRentTakingEffect FROM Rent r1 INNER JOIN Apartment a ON r1.ApartmentID=a.ApartmentID INNER JOIN Person p ON a.PersonID=p.PersonID WHERE r1.DateOfRentTakingEffect=( SELECT MAX(r2.DateOfRentTakingEffect) FROM Rent r2 WHERE r2.ApartmentID=r1.ApartmentID ) AND p.PersonID = 1
Razvan
PS. To be sure that you don't get too many rows you should also add a constraint to be sure that you don't rent the same apartment twice in the same day:
ALTER TABLE Rent ADD UNIQUE (DateOfRentTakingEffect, ApartmentID)
May 11, 2004 at 10:59 am
Thanks guys, it worked. Your help is very much appreciated. Many thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply