SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: help with JOIN

  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help with three table JOIN

    I have three tables below. I thought my SQL was working but I found a HUGE oversight. The SQL statement picks up ALL past contracts for a building.

    SELECT c.contract_ID, c.contract_Date
    FROM contract c
    LEFT JOIN building_owner j USING(building_ID)
    WHERE c.contract_Date > j.purchase_Date
    AND c.contract_Date < j.sell_Date

    It's pulling multiple copies of the same contract record.

    One way to handle this would be to add a "building_owner_ID" field to building_owner table. Then go back to ALL (LOTS) my SQL code and reference that field instead of building_ID. ANy ideas?

    TABLES
    =======================================

    building:
    building_ID
    address_1
    address_2
    ...

    building_owner:
    building_ID
    owner_ID
    purchase_Date
    sell_Date

    contract:
    contract_ID
    contract_Date
    ...
    Last edited by WebDevGuy; Aug 6, 2005 at 12:13.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    why are you using USING?

    it appears that the contract table does not contain a column called building_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My apologies, there is a building_ID in the contract table:

    contract:
    contract_ID
    building_ID
    contract_Date
    ...

    I used USING because it looked like in MySQL documentation it was interchangeable with "ON c.building_ID = j.building_ID". I tried it both ways and got the same results. (http://dev.mysql.com/doc/mysql/en/join.html)

    The more I think about it, the more it makes sense to add a new ID field to building_owner table and just reference that

    Also I am using a version of MySQL (3.23.55) that does not support sub-selects.

    Thanks for any help.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    how does adding a column to building_owner help? reference it where? why? how will that help your problem, which is that you are getting ALL past contracts for a building?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need to see all contracts in effect for a particular building at a particular time. As I intereated above, the sql statement I use:

    SELECT c.contract_ID, c.contract_Date
    FROM contract c
    LEFT JOIN building_owner j USING(building_ID)
    WHERE c.contract_Date > j.purchase_Date
    AND c.contract_Date < j.sell_Date

    pulls multiple copies of ALL contracts for that building regardless of date. I don't want ALL past contracts, only cojtracts for that building during a certain time period.

    It will help the problem because the SQL statement as it is now doesn't work.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you will have to look at your data

    the query seems right (other than the fact that it won't return contracts without buildings, i.e. the LEFT OUTER part won't work, it'll be the same as an INNER)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •