sql - 如何查询具有 2 个外键的表？

Question

我的 android 应用程序中有三个表。

Table Animal   Table Group   TableGroup_Animal
idAnimal       idGroup       idAnimal
AnimalName     groupName     idGroup

我想知道如何进行查询以获取组内的所有动物。我提出的查询是这样的

select
    Groups.groupName
from
    Groups,
    Animal,
    tableGrupo_Animal
where
    Animal.idanimal = tableGroup_Animal.fkanimal
    and
    Groups.idgrupo = tableGrupo_Animal.fkgrupo
group by
    group.groupName

还有这个(只显示第一行)

select
    *
from
    Animal
    INNER JOIN " + tableGroup + " ON Animal.idanimal = Group.idGroup
where
    Group.groupName = ' " + groupName +

正如我所说，我想让所有动物都进入一个组，我该怎么做？谢谢

Answer 1

我在 sqlite 中重新创建了您的数据库并对其进行了测试。此 SQL 工作正常。我建议您将表重命名为“组”，因为它是 SQL 中的保留字，并且您每次都必须像我在此 sql 中所做的那样重新勾选它。

select a.AnimalName, g.groupName 
    from Animal a 
        JOIN TableGroup_Animal tga ON a.idAnimal = tga.idAnimal
        JOIN `Group` g on g.idGroup= tga.idGroup;

这是我创建的架构和数据，可帮助任何尝试对此进行原型(prototype)设计的人。

    CREATE TABLE `Animal` (
    `idAnimal`  INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
    `animalName`    varchar(128)
);

CREATE TABLE `Group` (
    `idGroup`   INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
    `groupName` varchar(128)
);

CREATE TABLE `TableGroup_Animal` (
    `id`    INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
    `idAnimal`  integer,
    `idGroup`   integer
);

--Seeding animal
insert into `Animal` (animalName) values ('cat');
insert into `Animal` (animalName) values ('Rat');
insert into `Animal` (animalName) values ('Zebra');
insert into `Animal` (animalName) values ('Platypus');

--seeding group
insert into `Group` (groupName) values ('small');
insert into `Group` (groupName) values ('medium');
insert into `Group` (groupName) values ('large');

---seeding group animal pivot table
insert into TableGroup_Animal (idAnimal, idGroup) values (3,2);
insert into TableGroup_Animal (idAnimal, idGroup) values (3,3);
insert into TableGroup_Animal (idAnimal, idGroup) values (1,2);
insert into TableGroup_Animal (idAnimal, idGroup) values (4,2);
insert into TableGroup_Animal (idAnimal, idGroup) values (2,1);
);
insert into TableGroup_Animal (idAnimal, idGroup) values (2,1);