Trel Trel - 14 days ago 7
MySQL Question

Is there any alternate way to select the values in this case statement rather than subqueries

I know some degree of MySQL, but I'm not sure if there's a more correct way to do what I'm doing here.

#Select Ticket ID, Ticket Title, Assigned To
SELECT * FROM
(SELECT
ti.number, ti.ticket_id, foev.value AS "ticket_name",
CASE WHEN ti.staff_id = 0 AND ti.team_id = 0
then
"** Unassigned **"
ELSE
CASE when ti.staff_id = 0
THEN
(SELECT te.name FROM ost_team te WHERE te.team_id = ti.team_id)
ELSE
(SELECT CONCAT(st.firstname,LEFT(st.lastname,1)) FROM ost_staff st WHERE st.staff_id = ti.staff_id)
END
END AS "assigned_to"
FROM ost_ticket ti
LEFT JOIN ost_form_entry foe ON foe.object_id = ti.ticket_id AND foe.object_type = "T"
LEFT JOIN ost_form_entry_values foev ON foev.entry_id = foe.id
GROUP BY ti.ticket_id)
AS ticket_meta_list
ORDER BY ticket_id ASC;


Is there a more correct way to do those subqueries? I'm thinking there may be with joins that I'm less familiar with.

Answer

If your staff table has no staff_id with value 0, and your team table has no team_id with value 0, you can use left join in combination with coalesce:

SELECT    ti.number,
          ti.ticket_id,
          foev.value AS ticket_name,
          COALESCE(CONCAT(st.firstname,LEFT(st.lastname,1)),
                   te.name, '** Unassigned **') AS assigned_to
FROM      ost_ticket ti
LEFT JOIN ost_form_entry foe 
       ON foe.object_id = ti.ticket_id 
      AND foe.object_type = 'T' 
LEFT JOIN ost_form_entry_values foev 
       ON foev.entry_id = foe.id 
LEFT JOIN ost_team te 
       ON te.team_id = ti.team_id
LEFT JOIN ost_staff st 
       ON st.staff_id = ti.staff_id
GROUP BY  ti.ticket_id 
ORDER BY  ticket_id