如何查询具有父子关系的表

Question

>情况：

我有一个名为"word"的表格，其中包含一个带有相关翻译的单词。

| ID | name     | lang_id | parent_id |
|----|----------|---------|-----------|
| 1  | screw    | 1       | null      |
| 2  | schraube | 2       | 1         |
| 3  | vis      | 3       | 1         |

所以螺丝是没有父级的主要词。其他数据集与父级与parent_id相关联。

我想要什么：

我

需要一个查询来显示我搜索的单词和我输入的单词。

• 我想获取数据集 2 和 3，如果我将单词"schraube"从德语查询到法语。
• 我想获取数据集 1 和 3，如果我将单词"螺丝"从英语查询到法语。
• 。

我尝试过：

select word.id, word.name, word.lang_id, word.parent_id
from word
left join word w2 on word.parent_id = w2.parent_id 
WHERE w2.name = 'screw';
-- and word.lang_id = 2

不幸的是，结果不包含我输入的单词。此外，这将显示所有数据集，而不仅仅是具有特定语言的数据集。

Answer 1

您可以修改下面的查询以获得答案。

DECLARE @FromLanguageId SMALLINT = 2; --german 
DECLARE @ToLanguageId SMALLINT = 3; --french 
DECLARE @NAME NVARCHAR(300) = 'schraube'; 
--DECLARE @FromLanguageId SMALLINT = 1;  --english 
--DECLARE @ToLanguageId SMALLINT = 3;   --french 
--DECLARE @NAME NVARCHAR(300) = 'screw'; 
--Get  the mathing record 
; 
WITH ctematch 
     AS ( 
        --gets the matching record (child or parent)
        SELECT match.* 
         FROM   [word] match 
         WHERE  match.NAME LIKE @NAME), 
     --Join its sibling , parent and childs 
     ctefamilydata 
     AS (SELECT * 
         FROM   ctematch match 
         UNION 
         --Parent 
         SELECT parent.* 
         FROM   ctematch match 
                INNER JOIN [word] parent 
                        ON match.[parent_id] = parent.[id] 
         UNION 
         --Child 
         SELECT child.* 
         FROM   ctematch match 
                INNER JOIN [word] child 
                        ON child.[parent_id] = match.[id] 
         UNION 
         --Siblings 
         SELECT siblings.* 
         FROM   ctematch match 
                INNER JOIN [word] siblings 
                        ON match.[parent_id] = siblings.[parent_id]) 
--Filter and get the data 
SELECT * 
FROM   ctefamilydata Cte 
WHERE  Cte.[lang_id] = @ToLanguageId 
        OR Cte.[lang_id] = @FromLanguageId