We start from two tables:
Aminales: ID_animal, nombre, especie, incenter 1 Toby 2 1 2 Micke 2 1 3 Jake 2 1 4 Tom 2 1 Entradas: ID_entrada, ID_animal, municipio 1 1 Las Rosas 2 2 Coslada 3 3 Las Rosas 4 4 Madrid 5 3 Coslada 6 2 Coslada
What I need is to take the amount of animals that are within the center in the present (
incenter=1) and dogs(2) for each municipality. As seen in the tables, the animal 3 Jake has been delivered to the center of animals on two occasions,
ID_entrada 3 and 5. But we only need the
ID_entrada lower that this only appears as delivered from their municipality original, in this case
Las Rosas. No matter the amount of times it has come from other municipalities after being adopted and returned to the center.
The result of the query should be:
Madrid: Tom (total 1) Las Rosas: Toby, Jake (total 2) Coalada: Micke (total 1)
I've trying with this query:
SELECT animales.nombre, animales.ID_animal, entradas.ID_entrada FROM animales left JOIN entradas ON (animales.ID_animal = entradas.ID_animal AND incenter=1 AND especie=2 AND entradas.ID_entrada=(select min(ID_entrada) from entradas where municipio=$ID_municipio)
I cannot get it to work. Can you think of something?