Hello Michael, thx, I know normalisation.
BUT normalisation isn't always the best (fastest) way to store, or select the datas, this is why I don't use it most time. Often I'm testing my projects with normalisation and without and my last very big problem with big select statements is very fast without normalisation and with normalisation it was very slow. br Charlie > -----Ursprüngliche Nachricht----- > Von: Michael Stassen [mailto:[EMAIL PROTECTED] > Gesendet: Donnerstag, 17. August 2006 05:00 > An: mysql@lists.mysql.com > Cc: Mike van Hoof; Charlie Schaubmair > Betreff: Re: AW: find in list > > Charlie Schaubmair wrote: > > Hello, > > > > I want to do a query where I only gt the results by a > numeric value: > > > > select * from MyTable where 1 IN someFieldInMyTable > I > know this query doesn't work, but maybe anyone knows what I mean. > > > > 1 can be a value betwenn 1 and 23 > > someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21 > > Mike van Hoof wrote: > > try: > > > > SELECT * FROM MyTable WHERE someFieldInMyTable='%1%' > > This will also give you results like 21 > > What I > usally do in these cases is build the values like this: > > > > [1][2][3][21] > > > > And then the query: > > > > SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%' > > > > Mike > > Charlie Schaubmair wrote: > > Hello Mike, > > > > thx, but isn't there another way? > > Mike van Hoof wrote: > > maybe there is, but i don't know why... because when you > do a like query > it also finds the 21 when you do a like on the 1. > > what you maybe can do (not tested) is: > > > > SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield > FROM MyTable > WHERE wherefield='%,1,%' > > > > Now your someFieldInMyTable is CONCATed to > ,1,2,3,4,5,6,9,21, and over > that value the where clause is done... > > Mike van Hoof wrote: > > Sorry, query was wrong... has to be: > > > > SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield > FROM MyTable > WHERE wherefield LIKE '%,1,%' > > You've just discovered why this is not the way to store a > list of attributes. > Stuffing multiple values in a single cell is a bad idea. The > correct way to implement this is to store the attributes in a > separate table, one per row. > That is, instead of > > MyTable > ======= > MyTable_id other columns attributes > 1 ... 1,2,3,4,5,6,9,21 > 2 ... 5,7,13 > > you would do this: > > MyTable_attributes > ======= > MyTable_id other columns > 1 ... > 2 ... > > MyTable_attributes > ================== > MyTable_id attribute > 1 1 > 1 2 > 1 3 > 1 4 > 1 5 > 1 6 > 1 9 > 1 21 > 2 5 > 2 7 > 2 13 > > Then finding rows in MyTable which have attribute "1" becomes trivial: > > SELECT m.* > FROM MyTable m > JOIN MyTable_attributes ma ON m.MyTable_id = ma.MyTable_id > WHERE ma.attribute = 1; > > With a UNIQUE index on (attribute, MyTable_id) in > MyTable_attributes, this will be very quick. > > ==== > > That said, you can find what you want with your current > schema using the > FIND_IN_SET() function > <http://dev.mysql.com/doc/refman/4.1/en/string-functions.html>. > > SELECT * > FROM MyTable_attributes > WHERE FIND_IN_SET(1, someFieldInMyTable); > > This works so long as the values in someFieldInMyTable are > separated by commas. > If you switch to some other separator, such as enclosing > attributes in brackets, it won't work. > > Note that no index on someFieldInMyTable can be used for this > query, however, so it requires a full-table scan. > > Michael > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
