I am creating an MLS service for apartment communities.
The property information is stored in it's own table `property`
The individual unit information is stored in it's own table `floorplans`
the property table stores information about the individual apartment communities. Such as name,address,amenities, and contact info.
the floorplan table stores information about the individual units the complex has such available.
each property has an id and each floorplan has an identifyier field to match the property to the floorplan (`propid`).
my standard search function only searches the property information for area, or zipcode.
I have created an advanced search field to filter the properties that have floorplans matching the criteria from the advanced search box (#beds,#baths, price,etc.)
I used a left join select statement.
SELECT * FROM `property` AS p LEFT JOIN `floorplans` AS f ON f.propid=p.propid WHERE `zip` = '77075' AND f.bed = '3' AND f.bath = '2';
Each property has multiple floor plans and if more then one floorplan per property matches the search criteria (beds,baths) then each floorplan is pulled as a seperate result.
I want the results page to display each property one time as long as at least one of the property's floorplans matches the search criteria.
instead it displays each floorplan that matches per property so my results page displays the same property multiple times.
Any idea how to correct this without too much php coding possibly a simple addition to my MySql statment.
Any help would be appreciated.