SQL Query getting multple results from 3 table inner join

Question

I am running an sql query (using microsoft report builder)joining three tables and getting duplicates, i cant seem to stop this.

i have one table (tableA) that holds the relevant fields ownerID, propertyID, vacationDate. I want to find the name of the owner and the latest property they were in. (they may have been in several properties, i am only interested in the latest one) The Owners name is held on the Ownertable and linked by ownerID and the property address is found under the propertytable and linked by propertyID. I was trying to get each OwnerID with the latest vacation date and that particular propertyID then inner join the two tables to get the name and address. I have tried selecting 'max(vacation)' and then GROUP BY, but I am having problems because i am using report builder and have to use the group by clause on all fields not just the OwnerID, this brings up the latest vac date for each individual property aswell. Please can some one set me on the right track. Many thanks.

a sample of what i have done;

sorry im not on my pc to be give an exact sample here is a simplified version if this helps.

---

table A

ownerid      propertyid      vacation
1            2               1991
2            5               1993
1            3               1992
1            4               1997
2            6               2000
2            9               2007

so i want to select one row for each owner with the property id of the last vacation, for this example i would want this as below

ownerid     propertyid    vacation
1           4             1997
2           9             2007

I tried this below but get duplicates.

SELECT tableA.propertyid, tableA.ownerid, MAX(tableA.vacation), propertytable.propertyname, ownertable.ownername FROM tableA 

INNER JOIN propertytable on tableA.propertyid = propertytable.id

INNER JOIN Ownertable on tableA.ownerid = ownertable.id

GROUP BY tableA.property, tableA.owner, propertytable.propertyname, ownertable.ownername

Answer 1

I think you have to write a sub query to

" to get each OwnerID with the latest vacation date" as bellow

    SELECT
rs.ownerid,
rs.propertyid,
rs.vacation
FROM
(
SELECT
ownerid,
propertyid,
vacation,
MAX(vacation) OVER(PARTITION BY ownerid) AS maxDate
FROM  TableA

 ) as rs  

  WHERE rs.vacation = rs.maxDate