|
|
Posted by Hugo Kornelis on 10/22/06 21:51
On 19 Oct 2006 00:57:20 -0700, hardik wrote:
>hi friends i need help in this sql query
>
>i have table like,
>
>id fid
>__ _____
>autonumber text
>
>and i am storing values like
>
>id fid
>___________________________________
>1 1,2,3,4,5
>
>2 11,12,13,14,15
>
>now to find values i am using query
>
>sql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')
(snip)
Hi hardik,
You should really change this design. The fid column violates the
principle of first normal form. That makes many queries needlessly
complex and slow. A proper design would split the comma-delimited list
in fid into seperate rows:
id fid
1 1
1 2
1 3
1 1
1 1
2 11
2 12
2 13
2 14
2 15
Then, you'd just use SELECT * FROM better_table WHERE fid = '1'
(snip)
>now from this group some one give me the answer of this query
>
>select *
>from test
>where fid = '1' -- singleton
>or fid like '1,%' -- beginning of line
>or fid like '%,1,%' -- middle of line
>or fid like '%,1' -- end of line
Works, but there is a shorter kludge possible:
SELECT * FROM test WHERE ',' + fld + ',' LIKE '%,1,%'
>now this query is running perfectly in other database except msaccess
>2000.
Access doesn't use the ANSI standard wildcards for LIKE searches. In
Access, you have t replace the '%' character with '*'.
But the best solution is: fix the design!!
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|