1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Rep Power

    Simple Query Question

    I have a query that selects all the homes listed by a particular broker, brokerID = 4494....
    as defined in a cfparam in the header of the document.
    The query below kicks out all the homes for this particular broker. This woman works as a team with two other brokers. (4497 and 4417)
    I would like to have the database display the homes of three different brokers on the same page.

    I've simplified the query below and attached the actual .cfm.
    The actual .cfm calls the brokerID as defined in the cfparam, here i've simply hard coded it.
    Any help or clues would be greatly appreciated:

    <CFQUERY name="getHouses" datasource="#request.datasource#">
    select Distinct house.houseid, house.caption, house.state, house.address, cities.city, house.price, house.RentalPrice, houseStatus.status, company.realtyview, house.features, house.bedrooms, house.fullbath, house.halfbath, house.fireplaces, house.sellingprice
    From house, houseStatus, broker, houseBroker, cities, zip, company, companylocation
    Where (houseStatus.HouseStatusID = house.statusID) and ((houseStatus.status = 'Active') OR (houseStatus.status = 'CTS') OR (houseStatus.status = 'Preview')) and
    (houseBroker.houseID = house.houseID) and
    (houseBroker.brokerID = 4494) and
    (broker.companyLocationID = companyLocation.CompanyLocationID) and
    (companylocation.companyID = company.companyID)

    Order By house.price DESC </cfquery>

  2. #2
  3. No Profile Picture

    Join Date
    Jun 2002
    Raleigh, NC
    Rep Power
    If you also select the brokerId in the select statement, and order by brokerId, you can pass in a list of brokerIds and use an IN statement in your WHERE clause. Don't forget to ALWAYS use cfqueryparam for dynamic values like the list of brokerIds.

    Once you're getting houses for the 3 brokers, ordered by broker, you can use cfquery with the group attribute to output each broker's houses.

IMN logo majestic logo threadwatch logo seochat tools logo