match a value

[rupam_jaiswal]

Hi,

My table is like this

 

Id,Name,Location,Hobby

 

Condition is

- if location is 1 then possible values of Hobby can be a1,a2,a3,a4,a5

- if location is 2 then possible values of Hobby can be b1,b2,b3,b4,b5

- if location is 3 then possible values of Hobby can be c1,c2,c3,c4,c5

- if location is 4 then possible values of Hobby can be d1,d2,d3,d4,d5

 

Now I have to get the row whose hobby value is suppose a1.

How can I do this.I can't use like operator a1% as a100 will match then

Regards

[kickstart]

Hi

 

Really, redesign the database and split the hobby field onto a seperate table, with one row per bobby per person.

 

A short term bodge, use 3 likes:-

 

SELECT *

FROM SomeTable

WHERE (Hobby LIKE 'a1,%' OR Hobby LIKE '%,a1,%' OR Hobby LIKE '%,a1')

 

All the best

 

Keith

[rupam_jaiswal]

Hi

 

Really, redesign the database and split the hobby field onto a seperate table, with one row per bobby per person.

 

A short term bodge, use 3 likes:-

 

SELECT *

FROM SomeTable

WHERE (Hobby LIKE 'a1,%' OR Hobby LIKE '%,a1,%' OR Hobby LIKE '%,a1')

 

All the best

 

Keith

 

Hi,

Thanks for your reply.

But what will be the structure of new hobby table then?Will it be

PersonID, Hobby

and this hobby will have comma separated values like a1,a2...

[kickstart]

Hi

 

Probably just:-

 

Person table

Id, Name, Location

1, Huey, 1

2, Duey, 1

3, Luey, 2

 

Hobby table

Id, PersonId, Hobby

1, 1, a1

2, 1, a2

3, 1, a5

4, 2, a1

5, 2, a4

6, 3, b1

7, 3, b5

8, 3, b6

 

Makes it easy to search. Keep the location on the person table. Use joins to link them together.

 

Probably also be worthwhile having a table of hobbies with full descriptions of them on there (ie, one row per hobby).

 

All the best

 

Keith