0 votes

Query two tables and use of min()

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?

0voto

Santi92 Points 1111

Good , you can try the following:

SELECT COUNT(*) FROM animales a INNER JOIN entradas e 
ON a.id_animal=e.id_animal WHERE a.especie=2 AND a.incenter=1 GROUP BY e.municipio

0voto

I don't know very well-as you have defined the database, but with the following code, it works what you want:

CREATE TABLE animales(
    id INT NOT NULL PRIMARY KEY, 
    nombre VARCHAR(35), 
    especie INT, 
    incenter INT
);

CREATE TABLE entradas(
    id INT NOT NULL PRIMARY KEY, 
    id_animal INT, 
    municipio INT
);


SELECT COUNT(*), municipio 
FROM animales a JOIN entradas e ON a.id=e.id_animal 
WHERE incenter=1 AND especie=2 GROUP BY municipio;

The min() shouldn't be needed. If you do everything right, it's not going to be in two towns at the same time for both the incenter=1 already filtered out by the which is currently. In fact, if you take the minimum of id_entrada and the animal has changed several times, and municipality, will not be any tuple as the incenter in the minimum would be 0.

0voto

lorduncan Points 11

According to I run this query in PHPMyAdmin the results are desired. After only faltaria to check from php if the municipality belongs to the current column to add one to the total.

SELECT min(entradas.ID_entrada) as minimo, entradas.ID_entrada, animales.ID_animal, animales.nombre, entradas.municipio 
FROM entradas, animales 
WHERE animales.ID_animal = entradas.ID_animal AND animales.incenter =1 AND animales.especie =2
GROUP BY ID_animal
ORDER BY `animales`.`ID_animal` ASC

HolaDevs.com

HolaDevs is an online community of programmers and software lovers.
You can check other people responses or create a new question if you don't find a solution

Powered by:

X