CODEWITHSUNDEEP
mysqlsqlgroup-by
Mark C.
Mark C.

Reputation: 408

Complex sql query - can't figure it out

I need to form the join so that I have all tickets with all their notes. In addition, if the ticket has an appt_id and if the corresponding row in the appt table has a hash, we need to display fname and lname from the APPT table at the top of the messages (that are aggregated for each ticket). I have the join between tickets and notes working fine (see below), but the additional issue of adding in the appt table just came up and I am not sure how to approach the conditional issue of not having appt_id in all tickets and not having hash in all appt rows.

SELECT tickets.id as id, tickets.subject as subject, 
    group_concat(n.message SEPARATOR '<br><br>') as messages  
    FROM tickets LEFT JOIN tickets_notes n ON n.`ticket_id` = tickets.`id` 
    GROUP BY tickets.id;

Upvotes: 0

Views: 64

Answers (2)

forpas
forpas

Reputation: 164089

As I understand the requirement:

we need to display fname and lname from the APPT table at the top of the messages (that are aggregated for each ticket)

you can do it with CONCAT_WS() after you join the table appt also:

SELECT t.id, t.subject, 
  CONCAT_WS(
    '<br><br>', 
    CASE WHEN MAX(a.hash) IS NOT NULL THEN MAX(CONCAT(a.fname, ' ', a.lname)) END, 
    GROUP_CONCAT(n.message SEPARATOR '<br><br>')
  ) messages  
FROM tickets t 
LEFT JOIN tickets_notes n ON n.ticket_id = t.id 
LEFT JOIN appt a ON a.id = t.appt_id
GROUP BY t.id, t.subject;

Upvotes: 1

GMB
GMB

Reputation: 222432

Assuming that not more than one record in appt will correspond to a given ticket, you can just add one more LEFT JOIN:

SELECT 
    t.id, 
    t.subject,
    a.lname,
    a.fname,
    group_concat(n.message SEPARATOR '<br><br>') as messages  
FROM tickets t
LEFT JOIN tickets_notes n ON n.`ticket_id` = t.`id` 
LEFT JOIN appt a ON a.id = t.appt_id AND a.hash IS NOT NULL
GROUP BY 
    t.id,
    t.subject,
    a.lname,
    a.fname

The join condition on appt matches ticket(appt_id) on appt(id); there is an additional condition that appt(hash) should not be null.

Side notes:

  • it is a good practice to list all non-aggregated columns in the GROUP BY clause (starting MySQL 5.7, this is mandatory by default - most other RDBMS work the same way)

  • there is no need to alias columns like t.id as id: the original column name is used by default (you need alias when you want to assign a different name)

  • I modified the query to use table aliases consistently

Upvotes: 1

Related Questions