{"id":23,"date":"2012-10-01T06:36:58","date_gmt":"2012-10-01T04:36:58","guid":{"rendered":"http:\/\/icefront.info\/wp\/?p=23"},"modified":"2012-10-01T06:36:58","modified_gmt":"2012-10-01T04:36:58","slug":"mysql-select-field-replace-text-from-another-table","status":"publish","type":"post","link":"https:\/\/icefront.info\/index.php\/2012\/10\/01\/mysql-select-field-replace-text-from-another-table\/","title":{"rendered":"MySQL: Select field, replace text from another table"},"content":{"rendered":"

I have two tables, “products” and “brands”. In the “products” table, the product’s name is in the “name” field. The product’s brand is represented by the # (diez) character. To know which is the brand, there’s a “brand_id” field.<\/p>\n

So far we have:<\/p>\n

products table:\n  id: int\n  name: varchar\n  brand_id: int\n\nbrands table:\n  id: int\n  name: varchar<\/pre>\n
I was thinking in the other day, can I build the name only in SQL? The problem is that not all products have a brand…<\/p>\n
The first approach was the REPLACE() function<\/a>:<\/p>\n
function REPLACE(str, from_str, to_str)<\/pre>\n
Nice, but… There’s always a but… In the documentation it doesn’t states, when to_str<\/code> is NULL, the result is also NULL! Seems to me illogical, normally I expect the from_str<\/code>
\ncleared from str in this case…<\/p>\n
So, the query<\/p>\n
SELECT REPLACE(name, '#', (SELECT name FROM brands WHERE id = products.brand_id)) FROM products;<\/pre>\n
results in several NULL rows… This is wrong, since products without brands should be displayed as-is.<\/p>\n
What solves the problem, is the IF function<\/a>:<\/p>\n
IF(condition, true_value, false_value)<\/pre>\n
We use the “brand_id” field as condition, we replace the diez only if brand_id > 0<\/code>. The final query looks like this:<\/p>\n
SELECT IF(brand_id > 0, (REPLACE(name, '#', (SELECT name FROM brands WHERE id = products.brand_id))), name) FROM products;<\/pre>\n","protected":false},"excerpt":{"rendered":"
I have two tables, “products” and “brands”. In the “products” table, the product’s name is in the “name” field. The product’s brand is represented by the # (diez) character. To know which is the brand, there’s a “brand_id” field. So far we have: products table: id: int name: varchar brand_id: int brands table: id: int […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-23","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/posts\/23","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/comments?post=23"}],"version-history":[{"count":0,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/posts\/23\/revisions"}],"wp:attachment":[{"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/media?parent=23"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/categories?post=23"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icefront.info\/index.php\/wp-json\/wp\/v2\/tags?post=23"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}