Consulta SQL para unir dos tablas con agrupación

Question

Tengo dos mesas:

ticket_entries:

ticket_id entry_date  status responsible_agent
1 1516284485 open 1
2 1516284485 open 1
3 1516284485 open 1
4 1516284485 open 1

Ticket_logs

ticket_id entry_date responsible agent 
1 1516284486 1 
1 1516284487 1
1 1516284488 1
1 1516284489 1
1 1516284490 1
2 1516284485 1 
2 1516284482 1
etc

Estoy tratando de unirme a las tablas y obtener el resultado:

ticket_id entry_date  status responsible_agent
1 1516284490 open 1
2 1516284485 open 1
3 1516284485 open 1
4 1516284485 open 1

Con entry_date de ticket_logs mostrando el último registro para este ticket_id.

Traté de trabajar con ORDER BY y GROUP BY, pero luego obtuve el primer registro de ticket_logs en lugar del último:

      SELECT * FROM ticket_entries 
      JOIN ticket_logs 
           ON ticket_entries.ticket_id = ticket_logs.ticket_id 
     WHERE responsible_agent = '1' 
     GROUP BY ticket_entries.ticket_id 
     ORDER BY ticket_logs.entry_date DESC

sql database

0

Nikita Titow 18 ene. 2018 a las 18:43

Answer 1

Puede hacer la selección interna al principio, que agrupa los ticket_logs con el registro máximo y luego unirlo con ticket_entries.

SELECT  
    ticket_entries.ticket_id,
    ticket_entries.status,
    selectMaxEntryDate.max_date,
    ticket_entries.responsible_agent
FROM
    (
    SELECT ticket_logs.ticket_id as ticket_id,max(ticket_logs.entry_date) as max_date
    FROM  ticket_logs
    WHERE ticket_logs.responsible_agent = '1' 
    GROUP BY ticket_logs.ticket_id
    ) as selectMaxEntryDate
    JOIN 
    ticket_entries  ON ticket_entries.ticket_id = selectMaxEntryDate.ticket_id 
ORDER BY 
    selectMaxEntryDate.max_date;

1

Juraj 18 ene. 2018 a las 15:58

Answer 2

   Your question is a bit confusing as your data doesn't entirely make sense, but it seems like you're looking for a simple Group By.  You can't do a Select * and then just Group on one field.

    select te.ticket_id, max(ts.entry_date), te.status, te.responsible_agent
    from ticket_entries as te
    inner join ticket_logs as ts
    on te.ticket_id = ts.ticket_id
    group by te.ticket_id, ts.status, te.responsible_agent;

0

jackstraw22 18 ene. 2018 a las 16:01

Answer 3

SELECT
    e.ticket_id,
    MAX(l.entry_date) AS latest
    e.status,
    e.responsible_agent
FROM ticket_entries e
    INNER JOIN ticket_logs l
    ON e.ticket_id = l.ticket_id
WHERE e.responsible_agent = '1'
GROUP BY e.ticket_id, e.status, e.responsible_agent
ORDER BY MAX(l.entry_date) DESC

1

Kris Lawton 18 ene. 2018 a las 16:05

Consulta SQL para unir dos tablas con agrupación

3 respuestas

Nuevas preguntas

sql