inner join display last status

Question

inner join display last status

Navigation

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

2

I need to get data from 3 tables through a filter in 2 of them using the criteria Date and Status, the date being made in one table and the status in another.

It happens that when more than one status with the same name it lists all, I would like to get the last status.

query used:

SELECT
numero.id,
numero.numero,
status.status,
status.data
FROM numero
INNER JOIN dados ON numero.id = dados.id_numero
INNER JOIN status ON numero.numero = status.numero AND status.status = 'Confirmado'
WHERE dados.data BETWEEN '2015-04-10' AND '2015-04-10 23:59:59.997' ORDER BY dados.data

Online Statement: SQL Fiddle

mysql

asked by anonymous 29.04.2015 / 18:19

2 answers

0

Just make a GROUP BY numero.id

SELECT
  numero.id,
  numero.numero,
  status.status,
  status.data
FROM numero
INNER JOIN dados ON numero.id = dados.id_numero
INNER JOIN status ON numero.numero = status.numero AND status.status = 'Confirmado'
WHERE dados.data BETWEEN '2015-04-10' AND '2015-04-10 23:59:59.997'
GROUP BY numero.id
ORDER BY dados.data

29.04.2015 / 18:27

How to keep SDK and Java JRE installed and updated in a windows development environment Overlay a cursor on two divs

score 1 · Accepted Answer

I understand that when you say, "last status" you want to return only the one with the most recent date. If so, you can simply do it:

SELECT   numero.id,
         numero.numero,
         status.status,
         MAX(status.data) DATA
FROM numero
INNER JOIN dados 
   ON numero.id = dados.id_numero
INNER JOIN status 
   ON numero.numero = status.numero 
  AND status.status = 'Confirmado'
WHERE dados.data BETWEEN '2015-04-10' AND '2015-04-10 23:59:59.997' 
GROUP BY numero.id, numero.numero, status.status 
ORDER BY dados.data