SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Wizard
    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.

  2. #2
    SitePoint Guru
    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.

  3. #3
    SitePoint Wizard
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •