can anyone solve this sql query?

[hardik]

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 ........

[salad]

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

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's too bad your Fid doesn't have a space between numbers. Then you
could test for " 1,".

Maybe you can create then call a function. Like
Where SplitFid([Fid],1) = True

Public Function SplitFid(varFid As Variant, _
varSearch As Variant) as Boolean
and SplitFid would parse out the elements if the Fid using the Split
function to parse out each element of Fid and then enumerate the list
and see if the value to search for is in the list.

[Tim Marshall]

hardik wrote:

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%

I assume this is MS SQL compatible SQL as it is not at all familiar to
me from an Access Jet point of view... I'll use the ANSI 92/99 approach
that you seem to be using, though I don't know what the delimiters here:
`fid` mean so will omit them.

Assuming there are no spaces and just comma delimiters, you could use a
brute force method which sets the criteria against a fid sandwiched
between two commas. I'm not sure how to do this in MS SQL syntax and I
am at home and don't have my MS SQL translation documents with me, but
the following is how you'd do it in Oracle (the double pipes are the
equivalent of "&" in VBA code and MS Jet SQL) :

SELECT *
FROM
test12
WHERE
',' || fid || ',' LIKE ('%,1,%')

In non-ANSI compliant Jet SQL, the above would be:

SELECT *
FROM
test12
WHERE
("," & fid & ",") LIKE ("*,1,*")

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Kc-Mass]

Have the query place a comma at the beginning and end of your
field data, FID, then select based on ",1," not just "1". SQL follows:

SELECT ID, FID, "," & [fid] & "," AS FIDComma
FROM Data
WHERE ((("," & [fid] & ",") Like "*,1,*"));
Kevin C
"hardik" <ha**********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

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 ........

[paii, Ron]

"hardik" <ha**********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

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 ........

Tim Marshall and Kc-Mass answered the question for your structure, But you
should really consider normalizing fid. I have come to regret using the same
structure in a menu system and option bill of material. Storing fid in a new
table, will make query much easier and quicker.

tblfid
ID fID
1 1
1 2
1 3
1 4
1 5
2 11
2 12
2 13
2 14
2 15

[hardik]

paii, Ron wrote:

"hardik" <ha**********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

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 ........

Tim Marshall and Kc-Mass answered the question for your structure, But you
should really consider normalizing fid. I have come to regret using the same
structure in a menu system and option bill of material. Storing fid in a new
table, will make query much easier and quicker.

tblfid
ID fID
1 1
1 2
1 3
1 4
1 5
2 11
2 12
2 13
2 14
2 15

thanks paii but problem is solved and as in the normalization case real
tables are diffrent and data comes from diffrent tables i was just
giving sample for query . i am really thankful to all of you guys who
have help me that much.

thank you