开发者

MySQL: Looking up list of id's for many to many relationship

开发者 https://www.devze.com 2023-03-10 16:32 出处:网络
I have three tables: keywords, queries, and keywords_querie开发者_JAVA技巧s. keywords_queries has two columns; one linking to a keywords id and the other linking to a queries id. If I have the id of t

I have three tables: keywords, queries, and keywords_querie开发者_JAVA技巧s. keywords_queries has two columns; one linking to a keywords id and the other linking to a queries id. If I have the id of the query and a list of keywords that I want to link it to, what is the most efficient way to look up the id's of the keywords I have and insert them into the keywords_queries table? Obviously I could do a big SELECT query and use the results to build an INSERT query but is there some way to do it in one query? Also the keywords I'm looking up are guaranteed to be in the keywords table.


INSERT INTO keywords_queries
    SELECT query.id, keyword.id
      FROM keywords, queries
     WHERE keywords.id in (???)
       AND queries.id = ?


Not tested:

INSERT INTO keywords_queries (keywordid, queryid) VALUES (
    SELECT myQueryid, keywords.primaryid
    FROM keywords
    WHERE keywords.description IN ('a key','another key','and so on')
  )
0

精彩评论

暂无评论...
验证码 换一张
取 消