Compare MySQL Result Sets
Posted: Thu Feb 01, 2007 8:39 pm
Example:
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:
If I found that this query returned less than 15 results I would perform the featured advertisement query to supplement:
This is simple enough, and I can be absolutely sure that the two queries do not contain a similar record. However, if I change the query around and search by car features, I am unable to be certain that the two queries do not contain a shared record.
Again, I want to perform the query to select ads that do not match the search result set.
The above will however return the same records if multiple features are assigned to a car.
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?
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?