SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Thread: complex sql statement
-
Jan 13, 2005, 10:57 #1
- Join Date
- Dec 2004
- Location
- USA
- Posts
- 1,407
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
complex sql statement
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.
-
Jan 13, 2005, 13:14 #2
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
You must check that the period when a tenant occupied a building overlaps the period from ownershipStarted to ownershipEnded.
-
Jan 13, 2005, 13:19 #3
- Join Date
- Dec 2004
- Location
- USA
- Posts
- 1,407
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks