Issue
I am having an issue with a MySQL Query below:
SELECT DISTINCT t . * , c.customer_ref
FROM tickets t, ticket_items i, customers c
WHERE t.customer_id = c.customer_id
AND i.ticket_id = t.ticket_id
AND i.ticket_item_content LIKE '%reboot%'
ORDER BY ticket_last_reply_at DESC
LIMIT 0 , 30
At the moment, that will only select the tickets and ticket items if they have a customer ref or ID, I want it to select everything but they IF a customer ID is set then being back the customer ref.
I have tried inner joins also but can’t seem to get it working.
My aim is to select every ticket with content with reboot inside, whether it is assigned to a customer or not, but if it is then to return the customer’s details also.
Solution
Use left joins
SELECT DISTINCT t.*, c.customer_ref
FROM tickets t
LEFT JOIN ticket_items i ON i.ticket_id = t.ticket_id
LEFT JOIN customers c ON t.customer_id = c.customer_id
WHERE i.ticket_item_content LIKE '%reboot%'
ORDER BY ticket_last_reply_at DESC
LIMIT 0 , 30
Answered By – juergen d
Answer Checked By – Mary Flores (BugsFixing Volunteer)