Consult two tables and use of min ()

Question

Consult two tables and use of min ()

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

We start with 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 get the amount of animals that are inside the center at present ( incenter=1 ) and are dogs (2) for each municipality. As seen in the tables, the animal 3 Jake has been delivered to the animal center twice, ID_entrada 3 and 5. But we only need the lowest ID_entrada so that this only appears as delivered from its original municipality, in this case Las Rosas . It does not matter how many times you have entered 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 tried 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 can not make it work. Can you think of something?

mysql sql php

asked by lorduncan 19.11.2016 в 21:53

source

3 answers

Itextpdf 5.5.x with Bouncycastle Why is my DataTables table not sorted or filtered?

score 0 · Answer 1

0

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

answered by 19.11.2016 в 22:02

score 0 · Answer 2

I do not know very well how you have defined the database, but with the following code, what you want works:

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 () should not be missing. If you do everything right, it will not be in two municipalities at the same time so the incenter = 1 already filters you for the one that is currently. In fact, if you take the entry minimum and the animal has changed municipality several times, it will not count any tuple since the incenter at the minimum would be 0.

score 0 · Answer 3

As I execute this query in the PHPMyAdmin the results are as desired. Then you only need 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