如何在与另一个表关联的单词表中搜索单词列表? [英] How can I search for an a list of words in a table of words that associate to another table?

问题描述

以下是我已设置的3张桌子.通过索引零件表中零件名称中的所有单词，可以自动生成单词"和"word_part_mapping"表.

//'parts' table
+----------+--------------+
| part_num | part_name    |
+----------+--------------+
| 10111    | front bumper |
| 10112    | rear bumper  |
+----------+--------------+


//'words' table
+------+------------+
| id   | word       |
+------+------------+
| 1    | front      |
| 2    | bumper     |
| 3    | rear       |
+------+------------+



//'word_part_mapping' association table
+---------+----------+
| word_id | part_num |
+---------+----------+
| 1       | 10111    |
| 2       | 10111    |
| 3       | 10112    |
| 1       | 10112    |
+---------+----------+

如果用户搜索前保险杠"并且希望查询返回包含用户搜索的所有单词的结果，该如何查询?

SELECT p.* 
FROM words w 
LEFT JOIN word_part_mapping wpm ON w.id=wpm.word_id
LEFT JOIN parts p ON wpm.part_num=p.part_num
WHERE w.word='front' AND w.word='bumper'

很显然，以上查询不起作用，因为单词不能同时等于"front"和"bumper".如果我执行或"操作，则此方法有效，但是我不希望这样做，因为它返回的结果太多(数据库中超过50,000个零件).

=============================================

终于可以使用了...

SELECT p.* 
FROM words w 
LEFT JOIN word_part_mapping wpm ON w.id=wpm.word_id 
LEFT JOIN parts p ON wpm.part_num=p.part_num 
WHERE w.word IN('front','bumper') 
GROUP BY p.part_num 
HAVING COUNT(DISTINCT w.word) = 2

其中2是用户正在搜索的字词数量

解决方案

终于解决了...

SELECT p.* 
FROM words w 
LEFT JOIN word_part_mapping wpm ON w.id=wpm.word_id 
LEFT JOIN parts p ON wpm.part_num=p.part_num 
WHERE w.word IN('front','bumper') 
GROUP BY p.part_num 
HAVING COUNT(DISTINCT w.word) = 2

其中2是用户正在搜索的字词数量

Below are 3 tables I have setup. The 'words' and 'word_part_mapping' tables are generated automatically by indexing all of the words in the part's name from the parts table.

//'parts' table
+----------+--------------+
| part_num | part_name    |
+----------+--------------+
| 10111    | front bumper |
| 10112    | rear bumper  |
+----------+--------------+


//'words' table
+------+------------+
| id   | word       |
+------+------------+
| 1    | front      |
| 2    | bumper     |
| 3    | rear       |
+------+------------+



//'word_part_mapping' association table
+---------+----------+
| word_id | part_num |
+---------+----------+
| 1       | 10111    |
| 2       | 10111    |
| 3       | 10112    |
| 1       | 10112    |
+---------+----------+

How can I query this if the user searches for "front bumper" and I want the query to return results containing ALL words that the user searched for?

SELECT p.* 
FROM words w 
LEFT JOIN word_part_mapping wpm ON w.id=wpm.word_id
LEFT JOIN parts p ON wpm.part_num=p.part_num
WHERE w.word='front' AND w.word='bumper'

Obviously, the above query does not work because the word cannot equal both 'front' and 'bumper'. This works if I do OR, however I do not want that because it returns too many results (50,000+ parts in database).

==============================================

EDIT: Got it working finally...

SELECT p.* 
FROM words w 
LEFT JOIN word_part_mapping wpm ON w.id=wpm.word_id 
LEFT JOIN parts p ON wpm.part_num=p.part_num 
WHERE w.word IN('front','bumper') 
GROUP BY p.part_num 
HAVING COUNT(DISTINCT w.word) = 2

where 2 is the number of terms the user is searching

解决方案

Got it working finally...

SELECT p.* 
FROM words w 
LEFT JOIN word_part_mapping wpm ON w.id=wpm.word_id 
LEFT JOIN parts p ON wpm.part_num=p.part_num 
WHERE w.word IN('front','bumper') 
GROUP BY p.part_num 
HAVING COUNT(DISTINCT w.word) = 2

where 2 is the number of terms the user is searching

这篇关于如何在与另一个表关联的单词表中搜索单词列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！