MySQL Query with IF conditional

Question

i'm trying to write a MYSQL query that gets title, url_title, categories and also sets a URL field with a value based on the values of the categories column.

What I need to do is check the pipe-seperated Categories column (for example: 10|122|40|187) and set the value of a URL column to http://www.mydomain.tld with the url_title appended if the categories column contains category 10,40,80 else if none of those categories exist in the column then set the url as http://www.mydomain2.tld with the url_title appended.

I have tried to use a conditional below but i'm obviously doing this wrong;

SELECT exp_channel_data.entry_id AS entry_id, exp_channel_titles.title AS title, exp_channel_titles.url_title AS url_title, GROUP_CONCAT(cp.cat_id SEPARATOR '|') AS categories,
  IF('122,123,124,125,126,127,128,129,130,131,132,187' IN categories,
     (CONCAT('http://www.mydomain.tld',url_title AS 'URL')),
     (CONCAT('http://www.mydomain2.tld',url_title AS 'URL'))
  )
FROM data
FROM exp_channel_data
LEFT JOIN exp_channel_titles ON exp_channel_titles.entry_id = exp_channel_data.entry_id
LEFT JOIN exp_category_posts AS cp ON exp_channel_data.entry_id = cp.entry_id

Here is the table structure;

exp_channel_data
-------------
entry_id int 10

exp_channel_titles
-------------
entry_id int 10
title varchar 100
url_title varchar 100

exp_category_posts [this is the junction table that has been referred to]
-------------
entry_id int 10
cat_id int 10

I would be grateful for any advice.

Update: I have updated the query to what I'm using. I tried to simplify for the question, but realise this was a ba didea. I have also included the table structure above.

You should not store delimited lists in columns. Relational databases have a great data type for storing lists. It is called a table, specifically a junction table in this case. — Gordon Linoff, Sep 24 '14 at 11:03
Damn, I pasted the wrong url. I meant: Possible duplicate of [How can match a string of comma separated , irrespective of their position order in Mysql](http://stackoverflow.com/questions/25887713/how-can-match-a-string-of-comma-separated-irrespective-of-their-position-order/25887934#25887934). — GolezTrol, Sep 24 '14 at 11:06
Sorry, I should add that the categories are not stored as pipe separated. I have concatenated them into one field in my query. — doubleplusgood, Sep 24 '14 at 11:08
In that case, can you please show how the data is actually stored (table structure, example data), otherwise people will try to solve a problem that doesn't exist. — GolezTrol, Sep 24 '14 at 11:09

score 0 · Answer 1 · answered Sep 24 '14 at 11:06

Because you have a lousy data structure, you have to do this with a rather brute-force approach:

select title, url_title, categories,
       (case when find_in_set(122, replace(categories, '|', ',')) > 0 or
                  find_in_set(123, replace(categories, '|', ',')) > 0 or
                  . . .
                  find_in_set(187, replace(categories, '|', ',')) > 0
             then CONCAT('http://www.mydomain.tld', url_title AS 'URL')
             else CONCAT('http://www.mydomain2.tld', url_title AS 'URL')
        end)
FROM data;

You should be storing categories in a junction table, with one row per category and entity.

MySQL Query with IF conditional

1 Answers1