Building a MySQL query with CASE WHEN in it

[Jade Elizabeth]

I'm trying to change "id_board" into "id_type" but I also need to change the numbers...so I need any board id of 4 to become a type_id of 1 (essentially).

I'm hoping someone can help cause I have no idea what I'm doing.

Code Select Expand

SELECT
m.id_topic as id_work,
m.id_member,
m.subject as work_title,
m.body as work_desc,
m.poster_time,
m.poster_ip,
m.id_board as id_type =
CASE
WHEN '4' THEN '1'
WHEN '7' THEN '2'
WHEN '11' THEN '3'
WHEN '5' THEN '5'
WHEN '8' THEN '6'
WHEN '6' THEN '7'
WHEN '19' THEN '8'
WHEN '18' THEN '10'
WHEN '20' THEN '23'
WHEN '9' THEN '31'
WHEN '10' THEN '32'
ELSE 'ERROR'
END
FROM
smf_topics AS t INNER JOIN smf_messages AS m ON t.id_first_msg = m.id_msg
WHERE
m.id_board IN (4,5,6,7,8,9,10,11,18,19,20,71)
ORDER BY
m.id_topic

Also tried this one

Code Select Expand

SELECT
m.id_topic as id_work,
m.id_member,
m.subject as work_title,
m.body as work_desc,
m.poster_time,
m.poster_ip,
m.id_board as "id_type" =
CASE
WHEN m.id_board = '4' THEN '1'
WHEN m.id_board = '7' THEN '2'
WHEN m.id_board = '11' THEN '3'
WHEN m.id_board = '5' THEN '5'
WHEN m.id_board = '8' THEN '6'
WHEN m.id_board = '6' THEN '7'
WHEN m.id_board = '19' THEN '8'
WHEN m.id_board = '18' THEN '10'
WHEN m.id_board = '20' THEN '23'
WHEN m.id_board = '9' THEN '31'
WHEN m.id_board = '10' THEN '32'
ELSE 'ERROR'
END
FROM
smf_topics AS t INNER JOIN smf_messages AS m ON t.id_first_msg = m.id_msg
WHERE
m.id_board IN (4,5,6,7,8,9,10,11,18,19,20,71)
ORDER BY
m.id_topic

[Arantor]

I'd suggest not doing it in the query and having a map array in PHP to do the translation afterwards.

[Jade Elizabeth]

I'm exporting it to a CSV so I can add columns in excel and then import it into a new database. When I export it as it is there's a lot of editing to do when it comes to matching up ids....I want to avoid that. It's just a one time thing, when I'm moving this data for real it will be done by hand painstakingly (because the body of EVERY row needs to be edited, most titles do too).

[Arantor]

Well, the StackOverflow post did give you the correct syntax for CASE WHEN which is a lot of the problem.

CASE column WHEN oldvalue THEN newvalue (rinse repeat) ELSE other END as column_name

So I would assume...

Code Select Expand

SELECT
m.id_topic as id_work,
m.id_member,
m.subject as work_title,
m.body as work_desc,
m.poster_time,
m.poster_ip,
CASE m.id_board
WHEN 4 THEN 1
WHEN 7 THEN 2
WHEN 11 THEN 3
WHEN 5 THEN 5
WHEN 8 THEN 6
WHEN 6 THEN 7
WHEN 19 THEN 8
WHEN 18 THEN 10
WHEN 20 THEN 23
WHEN 9 THEN 31
WHEN 10 THEN 32
ELSE 'ERROR'
END as id_type
FROM
smf_topics AS t INNER JOIN smf_messages AS m ON t.id_first_msg = m.id_msg
WHERE
m.id_board IN (4,5,6,7,8,9,10,11,18,19,20,71)
ORDER BY
m.id_topic

[Jade Elizabeth]

Perfect! Thank you!

I see what I was getting wrong now, that makes sense. I went through a bunch of different tutorials and documentation too but I think they were showing something different. I've used case before, but I couldn't adapt the query I used in the past to be usable in this case.

Thanks so much!!