0

I have table:

tasks_whatwhom | CREATE TABLE tasks_whatwhom (
id int(3) NOT NULL AUTO_INCREMENT, 
name varchar(150) NOT NULL,
  message varchar(255) NOT NULL,
  filial_list text,
  client_list text,
  PRIMARY KEY (id),
  UNIQUE KEY name (`name`)
) ENGINE=Aria AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1

Field filial_list looks like '12,14'.

There is another table filials which contains addition information about filials.

SQL statement:

select * from filials where id IN(14,12) and status=1;

works as expect but below - returns zero. I don't understand why?

select * from filials where id IN(select filial_list from tasks_whatwhom) and status=1;
Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Andry
  • 121
  • 3
  • 12
  • Most times it's no good idea to save comma separated lists in fields. They are strings not lists of values. Use separate tables instead. You could use [FIND_IN_SET](https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_find-in-set) but it will not scale well. – VMai May 05 '14 at 13:06
  • What output do you get for "select filial_list from tasks_whatwhom;" ? – Joseph B May 05 '14 at 13:10
  • @VMai I know, but HTML form, submitted by user, contains a list of all filials and clients and I do not know better approach to store this information in DB – Andry May 05 '14 at 13:18
  • @Andry You can use a regular expression. Check out this discussion: http://stackoverflow.com/questions/1396084/regex-for-comma-delimited-list – Joseph B May 05 '14 at 13:21
  • @Andry Split those list before saving them to your database. You have to do this only once instead of processing bad data every time you query the data. That's much better. Abhik Chakraborty shows you how to use FIND_IN_SET that I mentioned. – VMai May 05 '14 at 13:23
  • @VMai Ok, but I don't understand which DB structure must be. I have table `tasks_whatwhom`. User opens form witch contains 1 field for message, one - for clients (left - list of available clients list, right - with clients witch must retrieve messages) and one field for filials (two columns too). If I will be store each client\filial as separated row instead of comma-separated list, i will duplicate message. Or I must store `message` filed in separate table?? – Andry May 05 '14 at 13:43
  • message remains in the current table. But you would create presumably two extra tables: one that maps the task to the filials and one that maps the task to the clients (I don't know if there is a correlation between filials and clients). You wouldn't change the user interface though. – VMai May 05 '14 at 13:50

2 Answers2

0
select * from filials where id IN(select filial_list from tasks_whatwhom) and status=1;

Well...you would have to have an ID that equals the value of Filial_list and has a filials.status value of 1

Your query works but your criteria does not. First of all filials_list is a text field, and the ID field is int. So why would you expect a match?

Second, you're storing a comma separated value in that field, which will not match on an int.

You will have to parse that column, every time, pull out the numbers and then see if you have a match.

Hituptony
  • 2,740
  • 3
  • 22
  • 44
  • I see I cannot solve my problem purely with help of SQL. Ok, with PHP first selection will select list of filials and second needed rows from `filials`. – Andry May 05 '14 at 13:13
0

You have a bad database design, you should never store data as comma separated and instead you should store one row for each related data.

One solution in your case would be as

select 
f.* 
from 
filials f 
inner join tasks_whatwhom tww on find_in_set(f.id,tww.filial_list);

Other than this if you change the DB structure and change filial_list to int and store one row for each of the referencing id from filials would be far more better.

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • Yes, I know. This is my first php\db application. Thanks :-) – Andry May 05 '14 at 13:32
  • Ok the more specific reason to split it to multiple rows is that when you have a large data in these tables `find_in_set` will be very slow and then you will need `index` so right from the beginning I would suggest to make it such that in future when you have huge amount of data you can apply index and the query will be way faster :) – Abhik Chakraborty May 05 '14 at 13:35
  • I think I must change DB structure. – Andry May 05 '14 at 13:45