Fetching record that have a particular value in a comma separated value in column [modified]
-
Good evening I have a table in database like following one --------------------------------------------------------------------- rollNo | Name | Subjects --------------------------------------------------------------------- 101 | abc | english, maths, social science, science, hindi 102 | def | maths, social science, science 103 | ghi | english, social science, hindi 101 | jkl | english, maths, social science, science, hindi ---------------------------------------------------------------------- now i want to select only those records which have "science" in the "Subjects" column so i use Select * from tblStudent Where Subjects LIKE "%Science%" but it also give the records that have "Social Science" ie record no 3 please help me thanks in advance
modified on Saturday, May 2, 2009 8:40 AM
-
Good evening I have a table in database like following one --------------------------------------------------------------------- rollNo | Name | Subjects --------------------------------------------------------------------- 101 | abc | english, maths, social science, science, hindi 102 | def | maths, social science, science 103 | ghi | english, social science, hindi 101 | jkl | english, maths, social science, science, hindi ---------------------------------------------------------------------- now i want to select only those records which have "science" in the "Subjects" column so i use Select * from tblStudent Where Subjects LIKE "%Science%" but it also give the records that have "Social Science" ie record no 3 please help me thanks in advance
modified on Saturday, May 2, 2009 8:40 AM
This is where you pay the price for BAD data design, If you were good you would create another table called Subject and link it to your student table in the correct foreign key data structure. Now you're screwed, you have to invent a work around for your crappy design. Options Are: Redesign you data structure correctly - this is the recommended solution. Play around with the text structure (', Science') will get most except where it is the first subject. Try and NOT like "Social Science" Create a split function based on the "," and filter on the required value (most sql devs have a split function floating around in their tools set)
Never underestimate the power of human stupidity RAH
-
This is where you pay the price for BAD data design, If you were good you would create another table called Subject and link it to your student table in the correct foreign key data structure. Now you're screwed, you have to invent a work around for your crappy design. Options Are: Redesign you data structure correctly - this is the recommended solution. Play around with the text structure (', Science') will get most except where it is the first subject. Try and NOT like "Social Science" Create a split function based on the "," and filter on the required value (most sql devs have a split function floating around in their tools set)
Never underestimate the power of human stupidity RAH
-
No doubt DB structure has million dollar worth butt still you can use you can use In "Select Col1 ,Col2 from tbl Where Col2 not In (SELECT Col2 FROM tbl ) " and can also use the XML as now is supported in SQL SERVER
Using
Inwon't work, you still need to split col2 into it's parts. Doing it with XML or a table var is the same thing, you still need a function to do the splitting of the course column.Never underestimate the power of human stupidity RAH
-
Using
Inwon't work, you still need to split col2 into it's parts. Doing it with XML or a table var is the same thing, you still need a function to do the splitting of the course column.Never underestimate the power of human stupidity RAH