Select just one row by date with JOIN

Question

I have these tables and I want to show this SELECT result

Fecha      Mes                  Dia                  Pendiente       Responsable
---------- -------------------- -------------------- --------------- -------------
2019-03-31 Marzo                Domingo              NULL            NULL
2019-04-01 Abril                Lunes                Entrevista 5    Ricardo
2019-04-02 Abril                Martes               Entrevista 15   Ricardo
2019-04-03 Abril                Miercoles            NULL            NULL
2019-04-04 Abril                Jueves               Entrevista 41   Ricardo

These are the tables:

Calendario
fecha      mes         dia
---------- ----------- -----------
2019-03-31 3           7
2019-04-01 4           1
2019-04-02 4           2
2019-04-03 4           3
2019-04-04 4           4
2019-04-05 4           5
2019-04-06 4           6
2019-04-07 4           7
2019-04-08 4           1

Mes
mes         nombre
----------- --------------------
3           Marzo
4           Abril

Dia
dia         nombre
----------- --------------------
1           Lunes
2           Martes
3           Miercoles
4           Jueves
5           Viernes
6           Sabado
7           Domingo

Responsable
responsable nombre
----------- --------------------------------------------------
1           Ricardo
3           Alberto

Pendientes
fecha      responsable pendiente
---------- ----------- ----------------
2019-04-01 1           Entrevista 5
2019-04-04 1           Entrevista 42
2019-04-08 3           Entrevista 97
2019-04-04 1           Entrevista 48
2019-04-02 1           Entrevista 15
2019-04-01 3           Entrevista 8
2019-04-04 3           Entrevista 43
2019-04-04 1           Entrevista 41

I want just the first row by date, but I can't, here is my SQL syntax:

SELECT c.fecha as Fecha, m.nombre as Mes, d.nombre as Dia, p.pendiente as Pendiente, r.nombre as Responsable 
FROM pendientes p RIGHT OUTER JOIN calendario c ON p.fecha = c.fecha 
LEFT OUTER JOIN responsable r ON p.responsable = r.responsable 
INNER JOIN mes m ON m.mes = c.mes 
INNER JOIN dia d ON d.dia = c.dia 
WHERE c.fecha between '20190331' and '20190404'

When I run it show all values with same dates included. I also tried with group by but with same results

score 0 · Answer 1 · answered Feb 13 '20 at 01:20

You can use next query:-

;with cte as (
  SELECT c.fecha as Fecha, m.nombre as Mes, d.nombre as Dia, p.pendiente as Pendiente, r.nombre as Responsable ,
  rank() over (partition by c.fecha order by p.pendiente) as [r]
FROM pendientes p RIGHT OUTER JOIN calendario c ON p.fecha = c.fecha 
LEFT OUTER JOIN responsable r ON p.responsable = r.responsable 
INNER JOIN mes m ON m.mes = c.mes 
INNER JOIN dia d ON d.dia = c.dia 
WHERE c.fecha between '20190331' and '20190404'
)
 SELECT  Fecha,  Mes,  Dia,  Pendiente,  Responsable 
from cte
where [r] = 1

also you can use Group by as next if you want a one row regardless its sort:-

SELECT c.fecha as Fecha, Min(m.nombre) as Mes, Min(d.nombre) as Dia, Min(p.pendiente) as Pendiente, Min(r.nombre) as Responsable 
FROM pendientes p RIGHT OUTER JOIN calendario c ON p.fecha = c.fecha 
LEFT OUTER JOIN responsable r ON p.responsable = r.responsable 
INNER JOIN mes m ON m.mes = c.mes 
INNER JOIN dia d ON d.dia = c.dia 
WHERE c.fecha between '20190331' and '20190404'
group by c.fecha

I Think, the first query is what you need.

Reference.

score 0 · Answer 2 · answered Feb 13 '20 at 06:51

Try this code for your output. This code is done on Mysql if you are using SQL Server then change "Limit" with "TOP".

 Select fecha,mes,Dia,pendiente,responsable from
    (Select fecha,mes,Dia,min(pendiente) as pendiente, responsable, 
    min(responsible)
    from (
    Select Q.*, E.nombre as Responsable
    from (Select A.fecha, B.nombre as mes, C.nombre as Dia, D.pendiente, 
    D.responsible 
    from Calendario A
    Left join
    (Select * from mes) B
    on A.mes=B.mes
    Left Join
    (Select * from Dia) C
    on A.dia=C.dia
    Left Join
    (Select * from Pendientes) D
    on A.fecha=D.fecha
    order by fecha
    )Q
    Left Join
    (Select * from Responsable) E
    on Q.responsible = E.resonable
    )Z group by fecha order by fecha limit 5
    )T

Select just one row by date with JOIN

2 Answers2