0

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 = "category_ticket_1"
AND a3.field_name_id = "category_ticket_2"

result query:

ticket_id   category_ticket_1    category_ticket_2
1           33                   44

i tried change AND a3.field_name_id = "category_ticket_2" into OR a3.field_name_id = "category_ticket_2" but it not work.

can someone help me to fixed this?

1
  • Read about "pivot".
    – Rick James
    May 28 at 1:46
2

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 = "category_ticket_1"
LEFT JOIN cc_queue_detail a3 
    ON a1.ticket_id = a3.ticket_id
    AND a3.field_name_id = "category_ticket_2"

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.

0
1

An OUTER JOIN would work nicely here with a GROUP BY roll up:

SELECT a1.ticket_id,
       MAX(a2.data_content) AS category_ticket_1,
       MAX(a3.data_content) AS category_ticket_2
  FROM cc_queue_detail a1
       LEFT OUTER JOIN cc_queue_detail a2 ON a1.ticket_id = a2.ticket_id AND a2.field_name_id = 'category_ticket_1'
       LEFT OUTER JOIN cc_queue_detail a3 ON a1.ticket_id = a3.ticket_id AND a3.field_name_id = 'category_ticket_2'
 GROUP BY a1.ticket_id

Any values that do not exist will return as NULL.

1
  • dude, i try your answer, and the result is: ticket_id category_ticket_1 category_ticket_2 (NULL) (NULL) 55 1 33 44 but, i change the RIGHT to LEFT, the result suit with what i expected ticket_id category_ticket_1 category_ticket_2 1 33 44 2 55 (NULL) Thanks a lot, dude! May 27 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.