搜索关键字并应用过滤器

Question

I am encountering a problem I cannot bypass by myself and I that's why I am posting this question. There are a lot of other posts out there that give me half of the answer and I don't really know how to get it done.

I have 3 tables that contain informations about an ad. One table is "ad_names", another is "ad_locations" and the last one is "ad_details".

Ad_Names has : ad_title, ad_description, ad_date_added
Ad_Locations has : ad_country, ad_region, ad_city
Ad_Details has : ad_price, ad_author, ad_active

Basically I want apply location and details filters for an ad with a certain title.

For example, I want to search "food" keyword in ad-titles and then apply filters like "only from Kansas" or "Only from Kansas + price higher than 500USD". How do I do it?

Answer 1

first you will need to join the tables. do you know if you have a foreign key on the tables? like is there an Ad_ID field on all 3 tables? if the tables are large you may also want to index the fields you are searching. once you have all the data you will "filter" it with a where clause.

http://www.w3schools.com/sql/sql_where.asp

select * from ad_names as A 
join ad_location as L on A.key=L.key 
join ad_details as D on A.key=D.key
where A.ad_title like '%food%' and L.ad_region = 'Kansas' and D.ad_prics > 500;

depending on how your database is setup, you may get a cartesian result there, so you would have to look at your join. maybe a left outer join limiting the join in an on statement instead of in the where. it is hard to say without more information.

Join types