I have a form allowing customers to search for a used automobile. The form has the following fields:
Car Model (pull down menu)
Car Color (pull down menu)
Car Year (pull down menu)
Car Features (multiple select menu)
Max Price (text field)
The customer must search using at least one of the above criteria, all of the criteria may be used if desired. On the search results page, I want 15 records to show per page. If the search results yield less than 15 total records, I will supplement the remainder (15 - # of search results) with featured advertisers. The featured advertisements will not come up in the natural search results. So, if someone searched for a green car and received 10 qualified records, I would have 5 records showing from featured advertisers that were NOT green cars.
The qualified search result query might look like:
Code: Select all
SELECT car_owner, car_location, car_condition FROM CAR_ADS WHERE car_model = '002' LIMIT 15Code: Select all
SELECT car_owner, car_location, car_condition FROM CAR_ADS WHERE car_model != '002' LIMIT <remainder>Code: Select all
SELECT CAR_ADS.car_owner, CAR_ADS.car_location, CAR_ADS.car_condition FROM CAR_ADS, CAR_FEATURES WHERE (CAR_FEATURES.feat_id = '014' AND CAR_FEATURES.car_id = CAR_ADS.car_id) AND CAR_ADS.car_adtype = 'featured' LIMIT 15Code: Select all
SELECT DISTINCT CAR_ADS.car_owner, CAR_ADS.car_location, CAR_ADS.car_condition FROM CAR_ADS, CAR_FEATURES WHERE (CAR_FEATURES.feat_id != '014' AND CAR_FEATURES.car_id = CAR_ADS.car_id) AND CAR_ADS.car_adtype = 'featured' LIMIT <remainder>Car# 1:
Features = air conditioning, 4 wheel drive, heated seats
Car# 2:
Features = air conditioning
Car # 3
Features = heated seats, tinted windows, automatic door locks
If we searched for a car with Air Conditioning, the results would be Car #1 and Car $2. The featured ads that are displayed after the qualified search results would display Car #1 and Car #3. Car #1 is repeated! Can anyone help me out with how I can accomplish this task?
Is there a simple way to compare two result sets and weed out the duplicate records?