英文:
Family table query mysql
问题
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
应该是：
选择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';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论