complex sql statement

[WebDevGuy]

My goal is to show building ownership and tenant history:

TABLE1
ownerName
buildingID
ownershipStarted(date)
ownershipEnded(date)

TENANT
buildingID
...

Here's what I have so far but it's adding up ALL the tenants for each owner - not differentiating which owner. In other words, building 23 has had 5 tenants over the past 10 years and 3 owners. When I run the query below, it displays that each owner had 5 tenants.

SELECT o.ownerName, o.ownershipStarted, o.ownershipEnded, COUNT(t.tenantID)
FROM owner o
INNER JOIN tenant t ON o.buildingID = t.buildingID
AND o.buildingID = 23
GROUP BY o.ownerName


Any ideas?
Thanks.

[swampBoogie]

You must check that the period when a tenant occupied a building overlaps the period from ownershipStarted to ownershipEnded.

[WebDevGuy]

I tried this:

SELECT o.ownerName, o.ownershipStarted, o.ownershipEnded, COUNT(t.tenantID)
FROM owner o
INNER JOIN tenant t ON o.buildingID = t.buildingID
AND o.buildingID = 23
AND t.dateOccupied > j.startDate
AND t.dateOccupied < j.endDate
GROUP BY o.ownerName

And it gives me NO results.

I might just have to do this in two separate queries since you can't do sub-queries in the MySQL version I am using.