2014-08-14 18:10
浏览 140


Well, I've a table called keywords and there are 2 columns.

1) kid
2) keywords (keyword value)

and I've another table called contact_details where there a column called keyword. In this keyword column I'm inserting many keyword from keywordstable. So 2 tables is look like this...

Keywords table:

kid    keyword
1      php     
1      mysql     
1      html     
1      css     
1      css3     
1      wp     
1      photoshop     
1      3d

contact_details table:

cid    name    phone   keyword
1      alex    123     php, mysql, hmtl
2      alex1   124     php, html, css3
3      alex2   125     wp, html, css
4      alex3   126     photoshop, 3d
5      alex4   127     html, 3d, php
6      alex5   128     mysql, wp, html

Now I've a search box which is searching people (name From contact_details table) by using keyword value. In search box, Search value could be few keywords. I mean it's could be php, mysql, html or could be php, 3d, photoshop.

So My question is : how can I write a Sql query to get the result ? I want to get all name which match the search keyword/s from contact_details table ?

Is there any field need to add in keywords table ? Can not get an IDEA :(

图片转代码服务由CSDN问答提供 功能建议

好吧,我有一个名为 keywords 的表,有2列。


我还有另一个名为 contact_details的表其中有一个名为 keyword 的列。 在 keyword 列中,我从 keywords 表中插入了许多 keyword 。 所以2个表看起来像这样......


  kid keyword 
1 php  
1 mysql 
1 html 
1 css 
1 css3 
1 wp 
1 photoshop 
1 3d 


1 alex 123 php,mysql,hmtl 
2 alex1 124 php,html,css3 
3 alex2 125 wp,html,css 
4 alex3 126 photoshop,3d  
5 alex4 127 html,3d,php 
6 alex5 128 mysql,wp,html 

现在我有一个搜索框,搜索人( name from contact_details table)使用关键字value。 在搜索框中,搜索值可能很少。 我的意思是它可能是 php,mysql,html 或者可能是 php,3d,photoshop

所以我的问题是:如何 我可以写一个 Sql查询来获得结果吗? 我想从 contact_details 表中获取与 search keyword / s 匹配的所有名称?

是否需要在 keywords 表中添加任何字段? 无法获得IDEA :(

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • douba8758 2014-08-14 18:28
    Select name from contact_details where keyword like '%<search keywords>%'

    Like search keyword key php then you need pass php in query and will get list of all name which having keywords as php'

    Select name from contact_details where keyword like '%php%'

    hope this will resolve your issue.

    Proper approach, Make kid as primary key in Keyword table

    Keywords table:
    kid    keyword
    1      php     
    2      mysql     
    3      html 

    Remove keywords column from contact_details table.

    contact_details table:
    cid    name    phone   
    1      alex    123     
    2      alex1   124     
    3      alex2   125 

    Make one more table which having many to many relationship and you need insert the relationship here so that no need touch again keywords and contact_details table again.

    kcid   kid_fk   cid_fk
    1      1        1     
    2      1        2
    3      1        3
    4      2        1    
    5      2        1 
    6      2        2
    7      2        3

    Sql query (Not tested you can also use alias)

    select name from contact_details join keyword_contact_mapping on kid_fk =(select kid from Keywords where keyword='php')
    打赏 评论
  • duanbing8817 2014-08-14 18:16

    Assuming you have the ID when the name is selected, you can use:

        SELECT Keyword from Keywords_Table
        WHERE ID = <ID>
    打赏 评论
  • douzhanhui5662 2014-08-14 19:12
    USE AdventureWorksDW2008R2
    IF OBJECT_ID('Keywords') IS NOT NULL
        DROP TABLE Keywords
    IF OBJECT_ID('Contact_Details') IS NOT NULL
        DROP TABLE Contact_Details
    IF OBJECT_ID('Keyword_ContactDetails') IS NOT NULL
        DROP TABLE Keyword_ContactDetails
    /* automate id's with identity. Or, do you want to specify the the keyword id manually? 
    CREATE TABLE Keywords(
        Keywords_ID INT IDENTITY(1,1) NOT NULL
        ,Keyword NVARCHAR(100)
      /* You must plan each column data type by careful consideration
         I am using the phone example here to demonstrate different business requirements
         the lenghts and datatype may need to change for localization
    CREATE TABLE Contact_Details
         Contact_Details_ID INT IDENTITY(1,1) NOT NULL
        ,First_Name VARCHAR(100)
        ,Last_Name  VARCHAR(100)
        ,Phone      VARCHAR(10)
        ,Phone_EXT  VARCHAR(3)
        ,Phone_International NVARCHAR(15)
        CONSTRAINT PK_Contact_Details PRIMARY KEY
    CREATE TABLE Keyword_ContactDetails
         Keyword_ID INT
        ,Contact_Details_ID INT
        ,DateTime_Created DATETIME
        CONSTRAINT PK_KeywordContact PRIMARY KEY
        /*Enforce referential integrity, 
          prevents adding keywords that don't exist
          prevents deleting a keyword if it is referenced   
        FOREIGN KEY (Keyword_ID) REFERENCES Keywords(Keywords_ID),
        FOREIGN KEY (Contact_Details_ID) REFERENCES Contact_Details(Contact_Details_ID)
    /* Populate keywords
    INSERT INTO Keywords(Keyword) VALUES ('PHP')
    INSERT INTO Keywords(Keyword) VALUES ('MYSQL')
    INSERT INTO Keywords(Keyword) VALUES ('HTML')
    INSERT INTO Keywords(Keyword) VALUES ('CSS')
    /* Add contact details
    INSERT INTO Contact_Details(
    /* Assign PHP Keyword to Abe Lincoln
    DECLARE @keywordID int = 0
            ,@contactDetails int = 0
    set @keywordID = (select Keywords_ID from Keywords where Keyword = 'PHP')
    set @contactDetails = (select Contact_Details_ID from Contact_Details where Last_Name = 'Lincoln')
    INSERT INTO Keyword_ContactDetails(
    SELECT * FROM Contact_Details C
    JOIN Keyword_ContactDetails KC
    ON KC.Contact_Details_ID = C.Contact_Details_ID
    JOIN    Keywords K
    ON K.Keywords_ID = KC.Keyword_ID
    打赏 评论

相关推荐 更多相似问题