0

I have a table. Let's name it user_errors

user_id     errors
    1       E001|E003
    1       E005|E001|E003
    3       E009|E002|E004
    2       E001|E005|E004|E006

I have another table which has error description. Let's say error_description

err_code    error_description
  E001          Error 01
  E002          Error 02
  E003          Error 03
  E004          Error 04
  E005          Error 05
  E006          Error 06
  E007          Error 07
  E008          Error 08
  E009          Error 09

I want to achieve below result.

Get distinct error codes from user_errors table. Just the distinct errors column

errors
 E001
 E002
 E003
 E004
 E005
 E006
 E009
praveen.menezes
  • 45
  • 8
  • Not really an answer so adding as a comment, but is it possible to change things so you only store one error code per row? – JustAnotherCoder Sep 07 '17 at 12:21
  • Possible duplicate of [Equivalent of explode() to work with strings in MySQL](https://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql) – Raymond Nijland Sep 07 '17 at 12:37
  • @TomRevell its not possible because I got 500k records. One row may have multiple errors hence it is separated using pipe (|) – praveen.menezes Sep 07 '17 at 12:39
  • @RaymondNijland That function will only return one error code. I want all the error codes mentioned in the string – praveen.menezes Sep 07 '17 at 12:42
  • @pmenezes you didn't read it all the way i think https://stackoverflow.com/a/13191958/2548147 will give you a list off errors. – Raymond Nijland Sep 07 '17 at 12:45
  • @RaymondNijland I'm working with [reportico](http://www.reportico.org/laravel/public/) library. And it doesn't allow me to call a Stored procedure. Hence I need a SQL code – praveen.menezes Sep 07 '17 at 12:58
  • @pmenezes Your database tables should be in 3NF to avoid such problems. Why do you save more than one value in a cell using a delimiter instead of using a new table with multiple rows per user? – Progman Sep 07 '17 at 15:17

1 Answers1

-1

Try this query

select distinct trim(substring_index(substring_index(errors, '|', n.n), '|', -1)) as values from user_errors t cross join (select 1 as n union all select 10 ) n order by values

aatish gore
  • 9
  • 2