December 15th, 2012, 09:15 AM
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>
December 15th, 2012, 01:46 PM
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.