-4

Been playing around with this for 2 hours and I just can't figure it out. In my MySql table I have a column named 'conflicts' that holds a comma separated string of 'menu_names', which is another column, that can't be clicked when itself is clicked.

Every 'menu_name' in the string needs for the row for that 'menu_name' to also have itself in it's conflict string.

Let's say that I have these four rows in my table;

        menu_name                conflicts
        ====================================================
        Apple             |      Banana,Carrot
        Banana            |      Apple,Carrot
        Carrot            |      Apple,Banana
        Bean              |      Carrot

In the above example we look at the conflicts of Apple and see Banana,Carrot. When checking the conflicts for Banana we find Apple and when checking Carrot we also fin Apple so that's good and I don't want that record. Banana and Carrot get passed over as well.

When we look at Bean we find it conflicts with Carrot. When checking Carrot we don't find Bean so that is the only record the query would return in this example.

Is this even possible to do with a single query?

Gary Scarma
  • 21
  • 1
  • 1
  • 4
  • 5
    Why are you storing data in a comma-separated list in the first place? If you normalized your data you wouldn't have this issue. – Taryn Sep 04 '13 at 21:11
  • http://stackoverflow.com/questions/4436591/how-can-i-search-within-a-table-of-comma-separated-values – Carlos Sep 04 '13 at 21:16
  • http://stackoverflow.com/questions/1987829/searching-from-comma-separated-value – Carlos Sep 04 '13 at 21:17
  • When I had some of these fields as their own related tables the people who wanted to manage their own data were hardly ever getting them set up correctly. Once I changed to this method they seemed to finally grasp the idea. Thanks Carlos, I think with those examples I can figure this out. – Gary Scarma Sep 04 '13 at 21:27
  • 2
    That's your reason for having unworkable data? Why not let your users enter comma-separated data and then you split it out into a sane schema without them knowing or caring how you actually stored it? – Aaron Bertrand Sep 04 '13 at 21:36

2 Answers2

3

First, Bluefeet is right. You should store your data in a normalized format. That is, you should have a table called something like MenuConflicts, with one column for Menu_Name and another for one Conflict.

That said, you can do what you want with an arcane join using find_in_set():

select *
from t t1 join
     t t2
     on find_in_set(t2.menu_name, t1.conflicts) > 0
where find_in_set(t1.menu_name, t2.conflicts) = 0;

The join matches any row where the second menu name is in the first conflict list. The where clause then checks the additional reciprocal condition.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

There are some answer on SO

that you must to do is a like search where the value taht you are looking for is used on a WHERE LIKE clause

At follow links you will find some examples on how to do.

I have copied one for you

SELECT *
FROM Table
WHERE Field LIKE 'Banana,%'
OR Field LIKE '%,Banana'
OR Field LIKE '%,Banana,%'
OR Field = 'Banana'

Now you must make a relationship between them

Community
  • 1
  • 1
Carlos
  • 4,299
  • 5
  • 22
  • 34