IN() not working as expected

jenni

I am fairly new to IN(), but the following does not make sense.

First, create a little table:

CREATE TABLE `users` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `profileTypes` text NOT NULL,
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

Insert some data into it:

INSERT INTO `users` VALUES (1, 'User 1', '1,2,3,4');
INSERT INTO `users` VALUES (2, 'User 2', '1,2');

This query returns both users

SELECT * FROM `users` WHERE 1 IN (`profileTypes`);

While this query returns no results

SELECT * FROM `users` WHERE 2 IN (`profileTypes`);

Is my field type for the profileTypes field wrong or do I just not understand how IN () works?

bastien

its usually

where someField in (select some_value from another_table [where clause])

Piranha

About IN it is as bastien says. I have no idea what your query actually does since it is normally not used that way. But my guess would be that SQL uses 0 for false and 1 for true, then your first query would mean where something exist in profile_types. I don't know for sure if it is that way, but it would explain your result.

But that is actually not why I'm posting. I am posting to ask you to search for "database normalisation" since there is a big problem with your last column.

MarkR

"IN" does not take a comma separated string as its parameter, rather a list of parameters. These are not the same thing.

You can't do it with a comma separated string.

If you're storing a comma separated list in a column, then the database isn't in first normal form- this is a poor technique and should not be used. Use a crossreference table instead.

Mark

jenni

Thank you folks. This is why I love this forum.
First of all, looking up Database Normalization was very helpful. It looks like I failed on "Atomicity: Each attribute must contain a single value, not a set of values."

The purpose of this field is to put people into profiles: order processing, technical support, etc. A person can be part of more than one group, so it needed to be a list of some sort.

In my example, I was trying to make the field double as a permissions field, which obviously is not going to work.

Unfortunately, I cannot nix the old stuff because other things use it. I am assuming then that I will have to create a separate permissions field?