PHP MYSQL query a join table for to get last row for parent table query

Question

I have two tables : ticket & history_ticket

Table ticket :

ticket_id  | ticket_desc   
 1         |  software   
 2         |  hardware   
 3         |  other

Table history_ticket :

history_id | ticket_id | message  | status    
 1         |  1        |  text    |  process 
 2         |  2        |  text    |  solve   
 3         |  3        |  text    |  process
 4         |  3        |  text    |  solve

I want result like this

ticket_id  | ticket_desc  | status 
 1         |  software    |  process
 2         |  hardware    |  solve
 3         |  other       |  solve

I've tried various joins and subselects, but no luck

Any help/directions will be much appreciated!

UPDATE : How if i change the result, like this

ticket_id  | ticket_desc  | last_status | count_message
     1     |  software    |  process    |     1
     2     |  hardware    |  solve      |     1
     3     |  other       |  solve      |     2

possible duplicate of [mysql: Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group) — Marc B, Feb 19 '13 at 19:31
possible duplicate: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — newtover, Feb 19 '13 at 19:34

score 0 · Answer 1 · answered Feb 19 '13 at 19:30

0

select distinct ticket.ticket_id, ticket.ticket_desc, history_ticket.status 
from ticket 
join history_ticket on ticket_id

This, as far as I remember, will choose a description and status at random if you have more than one. If you want to apply a specific rule to which one to pick, give more info and we can help you on that.

answered Feb 19 '13 at 19:30

Colleen

23,899
12
45
75

how does this solve the OP's problem? that's just a straight join. he wants only one row per record group. – Marc B Feb 19 '13 at 19:31

score 0 · Answer 2 · answered Feb 19 '13 at 19:31

0

Try

SELECT DISTINCT 
    tk.ticket_id, 
    tk.ticket_desc, 
    ht.status 
FROM ticket tk JOIN history_ticket ht ON tk.ticket_id = tk.ticket_id 
ORDER BY tk.ticket_id

answered Feb 19 '13 at 19:31

Kyle

4,421
22
32

score 0 · Answer 3 · answered Feb 19 '13 at 19:31

0

Try,

Select distinct t.ticket_id, t.ticket_desc, h.status 
from ticket t, history_ticket h
where t.ticket_id = h.ticket_id
order by t.ticket_id

answered Feb 19 '13 at 19:31

Teena Thomas

5,139
1
13
17

Mahmoud Gamal · Accepted Answer · 2013-02-20T09:56:55.920

Try this:

SELECT
  t.ticket_id, 
  ticket_desc, 
  ht.status
FROM ticket AS t
INNER JOIN history_ticket AS ht ON t.ticket_id = ht.ticket_id
INNER JOIN
(
   SELECT ticket_id, MAX(history_id) maxid
   FROM history_ticket
   GROUP BY ticket_id
) AS ht2 ON ht.history_id = ht2.maxid;

SQL Fiddle Demo

This will give you:

| TICKET_ID | TICKET_DESC |  STATUS |
-------------------------------------
|         1 |    software | process |
|         2 |    hardware |   solve |
|         3 |     Problem |   solve |

UPDATE 1

To get the count of messages for each ticket, you can simply include COUNT(history_id) AS sum_message in the subquery like this:

SELECT
  t.ticket_id, 
  ticket_desc, 
  ht.status, 
  ht2.sum_message
FROM ticket AS t
INNER JOIN history_ticket ht ON t.ticket_id = ht.ticket_id
INNER JOIN
(
   SELECT 
     ticket_id, 
     MAX(history_id) maxid, 
     COUNT(history_id) AS sum_message
   FROM history_ticket
   GROUP BY ticket_id
) AS ht2 ON ht.history_id = ht2.maxid;

Updated SQL Fiddle Demo

This will give you:

| TICKET_ID | TICKET_DESC |  STATUS | SUM_MESSAGE |
---------------------------------------------------
|         1 |    software | process |           1 |
|         2 |    hardware |   solve |           1 |
|         3 |     Problem |   solve |           2 |

Update 2

If you want to select names for the ids divisi_id, for simple values, you can use the CASE expression for this:

SELECT
  t.ticket_id, 
  ticket_desc, 
  CASE 
    WHEN t.divisi_id = 101 THEN 'Divisi A'
    WHEN t.divisi_id = 102 THEN 'Divisi B'
  END AS 'Divisi',
  ht.status, 
  ht2.sum_message
FROM ticket AS t
INNER JOIN history_ticket ht ON t.ticket_id = ht.hticket_id
INNER JOIN
(
   SELECT hticket_id, MAX(history_id) maxid, COUNT(history_id) AS sum_message
   FROM history_ticket
   GROUP BY hticket_id
) AS ht2 ON ht.history_id = ht2.maxid;

Updated SQL Fiddle Demo

This will give you:

| TICKET_ID | TICKET_DESC |   DIVISI |  STATUS | SUM_MESSAGE |
--------------------------------------------------------------
|         1 |    software | Divisi A | process |           1 |
|         2 |    hardware | Divisi B |   solve |           1 |
|         3 |     Problem | Divisi A |   solve |           2 |

For multiple values, you can put them in a temp table, or you can select them in a subquery and join the table to get the name like this:

SELECT
  t.ticket_id, 
  ticket_desc, 
  d.Divisi,
  ht.status, 
  ht2.sum_message
FROM ticket AS t
INNER JOIN history_ticket ht ON t.ticket_id = ht.hticket_id
INNER JOIN
(
   SELECT hticket_id, MAX(history_id) maxid, COUNT(history_id) AS sum_message
   FROM history_ticket
   GROUP BY hticket_id
) AS ht2 ON ht.history_id = ht2.maxid
INNER JOIN
(
  SELECT 101 AS divisi_id, 'Divisi A' AS Divisi
  UNION ALL
  SELECT 102             , 'Divisi B'
  ... -- here you put other values or you can join a temp table instead
) AS D ON t.divisi_id = D.divisi_id;

Updated SQL Fiddle Demo

Sorry i'm newbie for mysql query. But i try to implemented your scheme on my database. And i still confuse :( I update the question, please help. Thank you. — Hafiz, Feb 20 '13 at 07:06
@Hafiz - Why are you confused? Have you tried it? If it doesn't work can you please post the errors you got if any? or you can try to edit [the demo](http://sqlfiddle.com/#!2/0d086/3) example to explain the issue. — Mahmoud Gamal, Feb 20 '13 at 07:09
@Hafiz OK, See my edit, I think it is count of messages not the sum. — Mahmoud Gamal, Feb 20 '13 at 07:26
What if in [the demo](http://sqlfiddle.com/#!2/e79ae/1), i change ticket_id into hticket_id at history_ticket — Hafiz, Feb 20 '13 at 07:38
@Hafiz Just change it in the `ON` clause, make it `ON t.ticket_id = ht.hticket_id` instead of `ON t.ticket_id = ht.ticket_id`. Like in [**this updated sql fiddle demo**](http://sqlfiddle.com/#!2/e79ae/5). — Mahmoud Gamal, Feb 20 '13 at 07:41
Dear @Mahmoud Gamal, can you help me again. I have another column name 'divisi_id' in table ticket. I want add into query results, but change the value example 101 => 'Divisi A' , 102 => 'Divisi B', etc. Thanks before. [SQL Fiddle Demo](http://sqlfiddle.com/#!2/eba04/1) — Hafiz, Feb 20 '13 at 09:25
@Hafiz - Its al right, feel free to ask again any time if anything goes wrong. OK, but where does these values `'Divisi A'`, `Divisi B`, ... etc come from? Are there are more values or just limited number? And is it ok to create a temp table for them? — Mahmoud Gamal, Feb 20 '13 at 09:47
Hmmm.... How to add other value? [SQL Fiddle Demo](http://sqlfiddle.com/#!2/c7290/2) — Hafiz, Feb 20 '13 at 12:22
@Hafiz - Put `UNION ALL` for each select: [**Fixed SQL Fiddle Demo**](http://sqlfiddle.com/#!2/c7290/3) — Mahmoud Gamal, Feb 20 '13 at 12:43

PHP MYSQL query a join table for to get last row for parent table query

4 Answers4

SQL Fiddle Demo

UPDATE 1

Updated SQL Fiddle Demo

Update 2

Updated SQL Fiddle Demo

Updated SQL Fiddle Demo