CODEWITHSUNDEEP
sqlsqlite
jose
jose

Reputation: 75

How to query a table with 2 foreign keys?

I have three tables in my android app.

Table Animal   Table Group   TableGroup_Animal
idAnimal       idGroup       idAnimal
AnimalName     groupName     idGroup

I want to know how to do query in order to get all the animals inside a group. The query I made is this

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

And also this(That only shows the first row)

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

As I said I want to get all the animals inside a group, how can I do that? Thanks

Upvotes: 1

Views: 418

Answers (2)

José Nobre
José Nobre

Reputation: 5027

Do this query: 

 select * from Animal INNER JOIN tableGrupo_Animal ON Animal.idanimal = tableGrupo_Animal.fkanimal INNER JOIN Grupos ON Grupos.idgrupo = tableGrupo_Animal.fkgrupo where Grupos.nomegrupo='wwrt'

Upvotes: 1

Dom DaFonte
Dom DaFonte

Reputation: 1779

I recreated your database in sqlite and tested this out. This SQL works fine. I'd suggest that you rename your table "Group" as it's a reserved word in SQL and you'll have to back tick it every time as I do in this 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;

Here's the schema I created and data to help anyone trying to prototype this. 

    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);

Upvotes: 1

Related Questions