Select All Field (if field not exist, make field with null value) I have a table with this field and condition
table cc_queue_detail
ticket_id    field_name_id             data_content
1            category_ticket_1         33
1            category_ticket_2         44
2            category_ticket_1         55

I want to select for table cc_queue_detail and make them into like this:
ticket_id   category_ticket_1    category_ticket_2
1           33                   44
2           55 

i haved tried this query, but its only selected with the ticket_id 1
SELECT DISTINCT
a1.ticket_id AS 'ticket_id',
a2.data_content AS 'category_ticket_1',
a3.data_content AS 'category_ticket_2'
FROM 
cc_queue_detail a1
JOIN cc_queue_detail a2 ON a1.ticket_id = a2.ticket_id
JOIN cc_queue_detail a3 ON a1.ticket_id = a3.ticket_id
WHERE a2.field_name_id = &quot;category_ticket_1&quot;
AND a3.field_name_id = &quot;category_ticket_2&quot;

result query:
ticket_id   category_ticket_1    category_ticket_2
1           33                   44

i tried change  AND a3.field_name_id = &quot;category_ticket_2&quot; into OR a3.field_name_id = &quot;category_ticket_2&quot; but it not work.
can someone help me to fixed this?
Your issue is you're using an INNER JOIN (JOIN for short) between the same ticket_id of your cc_queue_detail table a1, and two different sets of rows from your second and third instance of the cc_queue_detail table, a2 and a3. This basically is saying the ticket_id has to match between a1, a2, and a3 always, but because of your WHERE clause, that's not possible, since a2 and a3 are different sets of rows from your cc_queue_detail table.
You'll want to use an OUTER JOIN, such as a LEFT OUTER JOIN (LEFT JOIN for short) to accomplish your goal, something like this:
SELECT DISTINCT
    a1.ticket_id AS 'ticket_id',
    a2.data_content AS 'category_ticket_1',
    a3.data_content AS 'category_ticket_2'
FROM cc_queue_detail a1
LEFT JOIN cc_queue_detail a2 
    ON a1.ticket_id = a2.ticket_id
    AND a2.field_name_id = &quot;category_ticket_1&quot;
LEFT JOIN cc_queue_detail a3 
    ON a1.ticket_id = a3.ticket_id
    AND a3.field_name_id = &quot;category_ticket_2&quot;

Now you'll get all rows back from a1, your entire cc_queue_detail table, and where there isn't a match between the other instances of the table (a2 and a3) there will be NULL values.