can anyone solve this sql query?

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%')

only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%
now any one have answer of this question then plz plz tell me ......hardik wrote:

Quote:

Originally Posted by

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%')
>
only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%
now any one have answer of this question then plz plz tell me ......

It seems like you are querying a database, that is not even in 1NF - you are
up to your neck in trouble. Rather than working on a single query you should
reorganise your database.

This particular query can be solved by

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

All of this assuming that you have no spaces in fid.

--
Regards,
Kristian Damm Jensen
"This isn't Jeopardy. Answer below the question."|||Am 16 Oct 2006 00:47:31 -0700 schrieb hardik:

...

Quote:

Originally Posted by

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%')
>
only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%
now any one have answer of this question then plz plz tell me ......

If ',' is your separator you can use:
SELECT * FROM test12 WHERE `fid` = '1' or `fid` LIKE ('1,%') or
`fid` LIKE ('%,1,%') or `fid` LIKE ('%,1')

bye, Helmut|||Thank you very much!
It works for me perfectly...
Kristian Damm Jensen wrote:

Quote:

Originally Posted by

hardik wrote:

Quote:

Originally Posted by

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%')

only problem in this query is it is selecting 1 and 11 and i require
only 1 as i am giving one in %1%
now any one have answer of this question then plz plz tell me ......

>
It seems like you are querying a database, that is not even in 1NF - you are
up to your neck in trouble. Rather than working on a single query you should
reorganise your database.
>
This particular query can be solved by
>
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
>
All of this assuming that you have no spaces in fid.
>
--
Regards,
Kristian Damm Jensen
"This isn't Jeopardy. Answer below the question."

|||Kristian Damm Jensen wrote:

Quote:

Originally Posted by

hardik wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>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%')
>>
>only problem in this query is it is selecting 1 and 11 and i require
>only 1 as i am giving one in %1%
>now any one have answer of this question then plz plz tell me ......

>
It seems like you are querying a database, that is not even in 1NF - you are
up to your neck in trouble. Rather than working on a single query you should
reorganise your database.

http://en.wikipedia.org/wiki/First_normal_form
You shouldn't store multiple values in a single column. Instead, change
your table to look like this:

id fid
-- --
1 1
1 2
1 3
1 4
1 5
2 11
2 12
2 13
2 14
2 15

and then you can simply do

select * from test12 where fid = 1

You should also use column names that are more descriptive than 'id' and
'fid', e.g. 'SalesOrderHeaderID' and 'SalesOrderLineID'.