CODEWITHSUNDEEP
mysqljoinleft-joininner-joinouter-join
shooby
shooby

Reputation: 95

mysql INNER JOIN but also NULL when not exist

Ok,

I'm trying to get this done the whole day but my knowledge in MySQL seems too limited.

I've got following tables:

time_entries
|id|comment|ticket_id|
| 1|foo    |        1|
| 2|bar    |        1|
| 3|baz    |        2|
| 4|lorem  |        3|
| 5|ipsum  |        4|

ticket
|id|name   |
| 1|ticket1|
| 2|ticket2|
| 3|ticket3|
| 4|ticket4|


custom_fields
|id|name   |
| 1|custom1|
| 2|custom2|
| 3|custom3|

custom_values
|id|custom_field_id|ticket_id|value|
| 1|              1|        1|   22|
| 2|              2|        1|   33|
| 3|              3|        1|   44|
| 4|              1|        2|   55|
| 5|              3|        2|   66|
| 6|              2|        3|   77|
| 7|              1|        4|   88|

What I'm trying to get is one line for each time_entry with the ticket information and the custom values. If no custom value is set for this ticket, the value in the result select must be empty or null:

select
|time_entries_comment|ticket_name|custom1_value|custom2_value|custom3_value|
|                 foo|    ticket1|           22|           33|           44|
|                 bar|    ticket1|           22|           33|           44|
|                 baz|    ticket2|           55|         NULL|           66|
|               lorem|    ticket3|         NULL|           77|         NULL|
|               ipsum|    ticket4|           88|         NULL|         NULL|

What I got so far is something like:

select 
  te.comment,
  t.name,
  cv1.value,
  cv2.value,
  cv3.value

  from time_entries te

  LEFT JOIN ticket t ON te.ticket_id = i.id

  LEFT JOIN custom_values cv1 ON t.id = cv1.ticket_id
  LEFT JOIN custom_fields cf1 ON cv1.custom_field_id = cf1.id AND cf1.id = 1

  LEFT JOIN custom_values cv2 ON t.id = cv2.ticket_id
  LEFT JOIN custom_fields cf2 ON cv2.custom_field_id = cf2.id AND cf2.id = 2

  LEFT JOIN custom_values cv3 ON t.id = cv3.ticket_id
  LEFT JOIN custom_fields cf3 ON cv3.custom_field_id = cf3.id AND cf3.id = 3

  WHERE t.id = 1;

But this gives me all matches.

I tried Inner Joins, but then I get no result, if there is no custom_value for this ticket. I also tried a Outer Join with UNION Left and Right with no success.

Any suggestions what to look for? What are the keywords here?

Thanks for your help

Upvotes: 3

Views: 4781

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

What you want is a process call pivot. This is mostly done with GROUP BY and MAX functions.

SELECT 
   time_entries.comment AS time_entries_comment
 , ticket.name
 , MAX(CASE WHEN custom_values.custom_field_id = 1 THEN custom_values.value ELSE NULL END) AS custom1_value
 , MAX(CASE WHEN custom_values.custom_field_id = 2 THEN custom_values.value ELSE NULL END) AS custom2_value
 , MAX(CASE WHEN custom_values.custom_field_id = 3 THEN custom_values.value ELSE NULL END) AS custom3_value
FROM 
 time_entries

INNER JOIN 
 ticket
ON
  time_entries.ticket_id = ticket.id

INNER JOIN
 custom_values  
ON
  time_entries.ticket_id = custom_values.ticket_id 

GROUP BY
    time_entries.comment
  , ticket.name

ORDER BY
  time_entries.id ASC

Result

time_entries_comment  name     custom1_value  custom2_value  custom3_value  
--------------------  -------  -------------  -------------  ---------------
foo                   ticket1             22             33               44
bar                   ticket1             22             33               44
baz                   ticket2             55         (NULL)               66
lorem                 ticket3         (NULL)             77           (NULL)
ipsum                 ticket4             88         (NULL)           (NULL)

Upvotes: 5

bjpreisler
bjpreisler

Reputation: 118

I think the LEFT JOIN is the way to go because it allows you to get NULL values. You simply need to add a GROUP BY statement at the end 

GROUP BY t.id

Upvotes: 1

Related Questions