views:

78

answers:

3

I just ran into a problem.

I know these integers, $integers: 3,5,15,20.

I only want to select the rows from this following table where all comma separated INT's from the field NUMBERS are found.

TABLE: number_table
Uid Numbers
------------------------
1   3,5,15    OK, since all of NUMBERS are in $integers
2   5,15,20   OK, since all of NUMBERS are in $integers
3   3,4,5,15  NOT OK, since 4 is not found in $integers
4   2,15,20,25  NOT OK, since 2 and 25 is not found in $integers

Is it possible to do a "for-each" on a comma separated string or another way to do this SELECT?

UPDATE: It sounds like this is not possible. I will leave it here for little while. Just a hint. When searching for something in a comma separated string then MySQL provides the WHEERE something IN (comma separated string). What I What I look for is someway to traverse a comma separated string using MySQL but that might not be possible.

Something like this would do it (pseudocode):

SELECT * FROM number_table WHERE each_commaseparated_substring(Numbers , 'IN (3,5,15,20)')
+3  A: 

It should NOT be comma separated fields. It must be rows in the related table.

Col. Shrapnel
"must" is strong... but normalized data (one row per item in another table) is far preferable.
Jason S
Good "best practice". But several applications work with commaseparated values. And in my job I also have to be able to those applications databases to pull data if needed. I quite often find relations stored as comma separated values when only used for one-2-many relations and there rarely are done a reverse lookup. Pro: a simpler database diagram. Con: database not normalized
Tillebeck
A: 

Maybe try with concate code using PHP and the implode() function.

Karthik
It seems like it is not possible to do in one call. Guess you are right, that I have to use php. If I find a better solution I will post it here and zap you right answer
Tillebeck
+1  A: 

I haven't tried this, and it's a bit ugly and quite possibly slow but you can try the following.

3,5,15,20

SELECT * FROM number_table
WHERE Numbers (LIKE '%,3,%' OR LIKE '%3,%') AND Numbers LIKE '%,5,%' AND Numbers LIKE '%,15,%' AND Numbers (LIKE '%,20,%' OR LIKE '%,20%')

You may be able to do something with REGEX. But at the very least you could use a stored procedure.

Updated for correctness

galford13x
Why the downvote? does this not produce the result he wants?
galford13x
Hi. That is the other way around searching for all $integers in filed Numbers. It should be all comma separated int's from field Integers that should be found in $integers
Tillebeck
Second thing. It is not enough to seach for %,3,%. For each INT it has to be a seach for '%,3,%' OR '3,%' OR '%,3' so beginning and end of string also is checked.
Tillebeck
Otherwise. Thanks for you feedback and help :-) Sorry to sound so negative I really appreciate all the answers and comments.
Tillebeck
Forgot: When searching a comma separated string you should us IN and not LIKE. See the page about a third down: http://www.webdevelopersnotes.com/tutorials/sql/tutorial_mysql_in_and_between.php3
Tillebeck
@Tilebeck: You are correct that %,3,% OR %3,% as well as %,20,% OR %,20% would be needed. However I would like to see you reconstruct the select state using the 'IN' clause. By what you said, the LIKE operator should never be used and shoudl always be replaced with the 'IN' clause and this is a false statement. Let me ask you this, can a wildcard be used with the 'IN' clause?
galford13x
sure, you are right. The same select cannot be used with IN. It will take another select statement. I was to stuck on the thought of iterating through each value in numbers and doing an IN on the $integer while iterating. Sorry.
Tillebeck
Perhaps you should remove the downvotes now you realise these are valid solutions to your problem.
Andy
Yep. I started out misunderstanding the answer. It is a correct sql call for a single row. But I need a call to get * from number_table (and not just a single row where I on forehand know the field Numbers). From the help given it seems it has to be done in two calls, therefore using php or similar between calls
Tillebeck
Never commented on the stored procedure. That may be a solution but I just do not know how to do that using MySQL/phpmyadmin
Tillebeck
It's been a while since I've looked at mysql stored procedures. You may want to start another question that asks for that specifically. The sql select statement I put should select all rows that match the 'LIKE' criteria shown. In other words it should return all rows that contain all of 3,5,15,20. If it is not doing so, let me know as I may be missing somthing.
galford13x