Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    7

    Unanswered: SQL Query Help Needed

    I'm working on a database for a real estate agency that keeps track of all of the properties their agents sell and/or list.

    I'm having a problem creating a specific report that shows the property address, name of the agent(s) listing and name of the agent(s) selling. A property can have more than one agent listing and selling (the agents split the commission).

    So I have a Properties Table, an Agents table and a WorkingAgents table that resolves the many to many relationship. The working Agents table has the PropID, AgentID, the role {Listing, Selling} and their commission.

    So how would I select the the property with all the agents who are listing it and all the agents who are selling it (there's a max of 2 agents working together to sell/list)?

    Anyone have any suggestions?

    Thanks a lot!
    -Wendy

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Without seeing some of your data (an example to preserve the confidentiality), I would suggest a union query to set up the property and agent sell with agent list

    Select Property, Agent where Role = 'List'
    Union
    Select Property, Agent where Role = 'Sell'

    Then you can base your report groupings on this query's Property field, and display the agent, role, and whatever else you need.

    I can go into more detail, but I think the union query is the 'thing' you are looking for.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    7
    Ok that would get me the listers listed in the same column as the sellers. But how would I get them to list in the same row?

    Here's some sample data to show you what I mean:

    PROPERTIES TABLE
    PropID -Address -Listing Price -Sale Price
    2 53 Midwest Way $100,000 $90,000
    4 98 Castle Way $200,000 $200,000
    8 1459 Southport $300,000 $275,000

    AGENTS TABLE
    AgentID -FirstName -LastName
    3 John Smith
    5 Tom Hanks
    6 Julia Roberts


    WORKINGAGENTS TABLE
    PropID -AgentID -Role
    2 3 Listing
    2 5 Listing
    2 6 Selling
    4 6 Selling
    4 5 Listing
    8 3 Selling
    8 5 Listing


    So from that, what I need is this:
    PropID -Seller -Lister
    2 6 3
    2 5
    4 6 5
    8 3 5

    Any idea how to do this? Should I set the tables up differently?

    I'm stumped!

    Thanks again,
    Wendy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select p.Address 
         , p.ListingPrice 
         , p.SalePrice
         , a.FirstName 
         , a.LastName
         , a.Role
      from (
           PROPERTIES p
    left outer
      join WORKINGAGENTS w
        on p.PropID = w.PropID
           )
    left outer
      join AGENTS a
        on w.AgentID = a.AgentID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    7
    That still only gets me the same results as a union would.

    Agh!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, sorry, i see now
    Code:
    select p.Address 
         , p.ListingPrice 
         , p.SalePrice
         , s.FirstName   as SellerFName
         , s.LastName    as SellerLName
         , l.FirstName   as SellerFName
         , l.LastName    as SellerLName
      from (((
           PROPERTIES p
    left outer
      join WORKINGAGENTS ws
        on p.PropID = ws.PropID
           )
    left outer
      join AGENTS s
        on ws.AgentID = s.AgentID 
           )  
    left outer
      join WORKINGAGENTS wl
        on p.PropID = wl.PropID
           )
    left outer
      join AGENTS l
        on wl.AgentID = l.AgentID      
     where ws.role='Selling'
       and wl.role='Listing'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    7
    That works great!

    Except for one thing....if there's more than one agent listing or selling a property, it doesn't work (eg it lists the property 4 times, when it should only show twice).

    Thanks,
    Wendy

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "(there's a max of 2 agents working together to sell/list)?"

    i guess you mean a maximum of two each, for a total of 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    7
    Yea so I guess this WILL work fine. Thanks so much!!!!

    I've been losing sleep over this!

    -Wendy

Posting Permissions

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