doujumiao5024
2014-01-20 22:32
浏览 36
已采纳

搜索关键字并应用过滤器

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?

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dousui7410 2014-01-20 22:58
    已采纳

    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

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • douhuijun3776 2014-01-20 22:49

    Assuming that Ad is a table with primary key id, which is a foreign key in each of the other tables (ad_id) then something along the lines of:

    Location:

    SELECT *
    FROM Ad A
    INNER JOIN Ad_Names N ON N.ad_id = A.id
    INNER JOIN Ad_Locations L ON L.ad_id = A.id
    INNER JOIN Ad_Details D ON D.ad_id = A.id
    WHERE N.ad_title LIKE '%food%' AND L.ad_city = 'Kansas'
    

    Price and location:

    SELECT *
    FROM Ad A
    INNER JOIN Ad_Names N ON N.ad_id = A.id
    INNER JOIN Ad_Locations L ON L.ad_id = A.id
    INNER JOIN Ad_Details D ON D.ad_id = A.id
    WHERE N.ad_title LIKE '%food%' AND L.ad_city = 'Kansas' AND D.ad_price > 500
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题