英文:

Family table query mysql

问题 {#heading}

1.获取Fay的阿姨

SELECT 阿姨.*
FROM 家庭 AS 阿姨
JOIN 家庭 AS 父母 ON 阿姨.parent_id = 父母.id
JOIN 家庭 AS 表兄弟姐妹 ON 父母.id = 表兄弟姐妹.parent_id
WHERE 表兄弟姐妹.id = 7 AND 阿姨.gender = 'female';

2.获取Hana的堂兄弟（男性）

SELECT 男性堂兄弟.*
FROM 家庭 AS 表兄弟
JOIN 家庭 AS 父母 ON 表兄弟.parent_id = 父母.id
JOIN 家庭 AS 祖父母 ON 父母.parent_id = 祖父母.id
JOIN 家庭 AS 男性堂兄弟 ON 祖父母.id = 男性堂兄弟.parent_id
WHERE 表兄弟.id = 10 AND 男性堂兄弟.gender = 'male';

英文:

I am trying to learn simple SQL with below family table structure, where parent_id is a relation of family.id (same table)

| id | name | gender | parent_id | |:---|:--------|:-------|:----------| | 1 | Adam | male | NULL | | 2 | Alvin | male | 1 | | 3 | Chris | male | 1 | | 4 | Michael | male | 1 | | 5 | Lala | female | 1 | | 6 | Matt | male | 2 | | 7 | Fay | female | 2 | | 8 | Percy | male | 3 | | 9 | Jackson | male | 3 | | 10 | Hana | female | 4 | | 11 | Hani | female | 4 |

I have the following questions that I have not finished and the sql progress that I made

1.Create a query to get the aunt of Fay

SELECT aunt.*
FROM family AS aunt
JOIN family AS parent ON aunt.parent_id = parent.id
JOIN family AS cousin ON parent.id = cousin.parent_id
WHERE cousin.id = 7 AND aunt.gender = 'female';

2.Create a query to get the male cousin of Hana

SELECT male_cousin.*
FROM family AS cousin
JOIN family AS parent ON cousin.parent_id = parent.id
JOIN family AS grandparent ON parent.parent_id = grandparent.id
JOIN family AS male_cousin ON grandparent.id = male_cousin.parent_id
WHERE cousin.id = 10 AND male_cousin.gender = 'male';

I know my query is wrong but that's the extent of my ability at the moment.

答案1 {#1}

得分: 1

应该是：

选择aunt.*
从家庭 作为 aunt
加入家庭 作为 parent 在 aunt.id = parent.parent_id
加入家庭 作为 fay 在 fay.parent_id = parent.id
其中 fay.name = 'Fay' 和 aunt.gender = 'female';

选择male_cousin.*
从家庭 作为 cousin
加入家庭 作为 parent 在 cousin.parent_id = parent.id
加入家庭 作为 hana 在 hana.parent_id = parent.id
加入家庭 作为 male_cousin 在 male_cousin.parent_id = parent.id
其中 hana.name = 'Hana' 和 male_cousin.gender = 'male';

英文:

Should be

SELECT aunt.*
FROM family AS aunt
JOIN family AS parent ON aunt.id = parent.parent_id
JOIN family AS fay ON fay.parent_id = parent.id
WHERE fay.name = 'Fay' AND aunt.gender = 'female';

and

SELECT male_cousin.*
FROM family AS cousin
JOIN family AS parent ON cousin.parent_id = parent.id
JOIN family AS hana ON hana.parent_id = parent.id
JOIN family AS male_cousin ON male_cousin.parent_id = parent.id
WHERE hana.name = 'Hana' AND male_cousin.gender = 'male';