1

IF I have a code like

$vals = "50,60,40";

IN SQL

"SELECT * FROM TABLE WHERE col IN( $vals )"

No if i have a value like 50 and i have col in SQL contain "50,40,60"

How can i selected it

I mean the inverse of IN Syntax

if i have table in sql contain values like

Firstname | Lastname | Age | Hobbies
Ahmed     | Ali      | 50  | run,swim
Mohammed  | Ahmed    | 30  | run

and if i want who can swimming in Hobbies

NikiC
  • 100,734
  • 37
  • 191
  • 225
Ahmed
  • 2,823
  • 3
  • 16
  • 9
  • It's nearly always an indication that your schema isn't correct if you have column that contains comma-separated values. Typically you'd use a separate table for the values rather than store them in a single column separated by commas. – tvanfosson Jun 05 '11 at 14:42
  • I don't see where he has a column that has a csv in it. I think he is assuming that the database will decompose his csv into a series. Obviously it doesn't and try to see if there is a given column with a value of "50,60,70", which I doubt it will. He needs a split function first so that each value is seen independently in his $vals variable. – hivie7510 Jun 05 '11 at 14:49
  • `FIND_IN_SET(value,column) !=0` can help , but you should take @tvanfosson's advice, as it is both a maintenance nightmare and cannot take advantages of keys, so it will be a lot slower then needed. – Wrikken Jun 05 '11 at 14:53

3 Answers3

6

I mean the inverse of IN Syntax

You could use the 

NOT IN

operator if you had a good table structure.

You should make a table for the Hobbies and then add a relanshionship with your person table, baecause as now the only way to get this done is with an ugly SQL containing lot of LIKE condition.

dynamic
  • 46,985
  • 55
  • 154
  • 231
  • No - that's not what he wants. – tvanfosson Jun 05 '11 at 14:43
  • So for you what is `the inverse of IN Syntax` ? – dynamic Jun 05 '11 at 14:44
  • @yes123 - he wants something like `where col like '%50,' or col like '%,50,%' or col like '%,50' ... ` since he has a single value and the column may contain multiple values. The larger problem is that the table structure is likely wrong and the values shouldn't be stored in a single column but rather in a separate table. – tvanfosson Jun 05 '11 at 14:49
  • 1
    @yes: I know, I like to start answering a question after only reading the title, too. But if you had read the actual question you would know that what you have written does not answer it. – NikiC Jun 05 '11 at 14:50
  • @nikic: acutally before he edited this question there were no indication this would not answer him. only now he added his table structure. You should not make false accusation. – dynamic Jun 05 '11 at 14:52
  • 1
    @yes: "if i have a value like 50 and i have col in SQL contain "50,40,60" How can i selected it". PS: I'm not trying to offend you or something like that. I am just asking you to remove this off-topic answer. – NikiC Jun 05 '11 at 14:54
  • @yes123 - the sentence `No if i have a value like 50 and i have col in SQL contain "50,40,60"` was in the original and was the basis for my comment. – tvanfosson Jun 05 '11 at 14:54
  • @tvanfosson: did you get it's not my fault if he asked the contrary of IN operator? what you are trying to say it's anyway not the contrary of the IN operator. So instead to accuse me why just don't leave a comment for the OP to suggest him to explain himself better? Anyway I expanded my answer to address this problem. – dynamic Jun 05 '11 at 14:57
  • @yes: Really, if you see that you have answered a question inappropriately (for whatever reason, may it be a not-so-well formulated question or you being in the wrong tab or whatever) you can just remove your answer (the "delete" button is under your answer). There is little sense in arguing with other people why you answered it the way you did. We understand that the question title might be misleading. It just doesn't matter, delete it and you're done... – NikiC Jun 05 '11 at 15:01
  • @yes - I agree that the title is somewhat misleading. Just pointing out that this isn't an answer for the actual text of the question for future readers. – tvanfosson Jun 05 '11 at 15:14
2

I recommend changing your schema to store the values in a separate table with a FK to the original column.

 People
 ID - PK
 FirstName
 LastName 

 Hobbies
 ID -- PK
 Person_ID -- FK to People
 Hobby -- store your comma-separated values here

Then you can do

select People.ID, People.FirstName, People.LastName
from People
   inner join Hobbies on People.ID = Hobbies.Person_ID
where Hobbies.Hobby = 'Swimming'

An even better structure might have 3 tables, allowing you to maintain a set of hobbies independently of the people and simply keeping a relation between each person and their hobbies.

People
ID 
FirstName
LastName

Hobbies
ID
Hobby

PeopleHobbies
ID
Person_ID
Hobby_ID

As always, consider your query needs and create suitable indices on the tables to allow speedy lookups and joins.

tvanfosson
  • 524,688
  • 99
  • 697
  • 795
  • I'm not sure about needing the PK in TableB (I'd just make the PK (ID_A,val3), with an extra index(val3) to speed things up), but essentially, yes, do this. – Wrikken Jun 05 '11 at 14:55
  • @Wrikken - I always use an artificial PK and in this case it would be very helpful since the relation is one to many and an artificial primary key would allow me to easily delete a single row from the relation. An even better structure would probably use 3 tables, People, Hobbies, and PeopleHobbies. – tvanfosson Jun 05 '11 at 15:11
  • Ack, 3-table options can be better with a limited set of hobbies. Never liked articial PK's in relationship tables as it's non-data, I avoid them if I can, but indeed, you'll have to provide both fields to a query if you don't. – Wrikken Jun 05 '11 at 15:16
  • @Wrikken - you might be interested in this article: http://www.agiledata.org/essays/keys.html or this question: http://stackoverflow.com/questions/3747730/relational-database-design-question-surrogate-key-or-natural-key – tvanfosson Jun 05 '11 at 15:22
  • It is certainly debatable, but I often work in environments with a lot of clients, and deleting/modifying something based on a natural key is in my experience in environments with more volatile data (someone adds a relation, another/the same one removes it, re-adds it, re-removes it, etc.) more consistent in stateless protocols like HTTP (which I assume this is for as PHP is in the tags). But it's of course not written in stone, depends on the circumstances (for instances: all fields in the relation should be reasonably small, preferably int), etc., etc. – Wrikken Jun 05 '11 at 15:31
  • But let's not make this Q/A a debate about that: point is: your solution to the problem is infinitely better then `FIND_IN_SET` :) – Wrikken Jun 05 '11 at 15:32
2

Check the FIND_IN_SET() function:

"SELECT * FROM TABLE WHERE FIND_IN_SET('swim', Hobbies) > 0"
BenMorel
  • 34,448
  • 50
  • 182
  • 322
  • 1
    This is only valid for MySQL. That's a reasonable assumption since the question is tagged PHP, but we use PHP with MSSQL as well as MySQL and it wouldn't work for MSSQL. – tvanfosson Jun 05 '11 at 14:57
  • Good point. Also, still based on this assumption, if your Hobbies list is not likely to evolve, declaring it as a `SET` will be faster, as it will use bit arithmetic instead of string comparison. – BenMorel Jun 05 '11 at 15:02
  • @yes: this works even on a non-SET column: `SELECT FIND_IN_SET('swim', 'run,swim')` => `2` – BenMorel Jun 05 '11 at 15:17
  • @Yes123: yes it does work, check the docs, this works on a comma-separated plain char/text column also. – Wrikken Jun 05 '11 at 15:18