SELECT a.*,
(select count(*) from f4s56_itcs_fields c where c.fid=a.id) nbfields,
(select b.username from f4s56_users b where a.created_by = b.id) username
FROM f4s56_itcs_forms a
where a.created_by=326
or a.group in(2,3,9)
here, "a.group" could be 2 or 3 or 4 or 2,3,4,5,6 etc. Means, group may be single value or multiple valued. when it is multiple valued, data is not being retrieved. how to fix that?
IM not sure if i understand your query but try this .
SELECT a.*,b.username, count(*) from f4s56_itcs_forms a
INENR JOIN f4s56_users b
on a.created_by = b.id
INNER JOIN f4s56_itcs_fields c
on c.fid=a.id
where a.created_by=326 or a.`group` in(2,3,9)
NOTE: that you have i think column called group so you should escape it by backticks like that
`group`
If you are storing multiple values in one column then you can do this.
where a.created_by=326 or FIND_IN_SET(2, a.`group`) > 0
or FIND_IN_SET(3, a.`group`) > 0
or FIND_IN_SET(9, a.`group`) > 0
when it is multiple valued, data is not being retrieved.
Based on newfurniturey's answer, you can use find_in_set() to search through comma separated values.
SQLFiddle: http://sqlfiddle.com/#!2/1f514/1
Unfortunately you would need to convert:
in(2,3,9) to
FIND_IN_SET('2', a.'group') > 0 or FIND_IN_SET('3', a.'group') > 0 or FIND_IN_SET('9', a.'group') > 0
Resulting in an SQL:
SELECT a.*,
(select count(*) from f4s56_itcs_fields c where c.fid=a.id) nbfields,
(select b.username from f4s56_users b where a.created_by = b.id) username
FROM f4s56_itcs_forms a
where a.created_by=326
or FIND_IN_SET('2', a.`group`) > 0
or FIND_IN_SET('3', a.`group`) > 0
or FIND_IN_SET('9', a.`group`) > 0;
If the above is not the case, your issue may be related to data. I have created a test schema based on your query and results are returned.
SQLFiddle: http://sqlfiddle.com/#!2/3763b/1
create table f4s56_itcs_fields
(
field_id int,
field_name varchar(20),
fid int
);
create table f4s56_users
(
id int,
username varchar(100)
);
create table f4s56_itcs_forms
(
id int,
created_by int,
`group` int
);
insert into f4s56_users values (326, 'user_326');
insert into f4s56_users values (327, 'user_327');
insert into f4s56_itcs_fields values (1,'name',1);
insert into f4s56_itcs_fields values (2,'last name',1);
insert into f4s56_itcs_fields values (3,'telephone',1);
insert into f4s56_itcs_forms values (1, 326, 2);
insert into f4s56_itcs_forms values (2, 326, 3);
insert into f4s56_itcs_forms values (2, 326, 9);
If I understand correctly, the f4s56_itcs_forms.group column (or a.group aliased), is a string-type column that can contain single or multiple values:
a.group
-------
1
2
4
2,5
2,3,5,7
If this is correct, you won't be able to do a direct comparison checking for a "list of values" that are contained "in the list of values in a.group". You can, however, do individual-value checks using MySQL's FIND_IN_SET() using a.group as the set you're looking at.
For instance, let's say you want to find all records that have the value 2 in a.group:
...
WHERE
FIND_IN_SET(2, a.`group`) > 0
If you have multiple values you want to check for, you'll need to add each of them separately:
...
WHERE
FIND_IN_SET(2, a.`group`) > 0
AND FIND_IN_SET(3, a.`group`) > 0
AND FIND_IN_SET(9, a.`group`) > 0