[SOLVED] Inner Join Search Query

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)

Leave a Reply

Your email address will not be published.