Re: how to do this query

2010/1/9 Wes James <comptekki(at)gmail(dot)com>:
> I have two tables:
>
> students
>  stu_name
>  schols_selected
>
> scholarships
>  schol_name
>  short_name
>
> schols_selected is made up of scholarships the students have selected,
> the field content will look like schol1:schol2:schol3
>
> I need a select that does something like this
>
> select schol_name, short_name, stu_name, schols_selected
> from scholarships, students
> where short_name is in schols_selected
>
> I have tried:
>
> where short_name like '%' || schols_selected || '%'
>
> but i'm not sure how to include a field result in between like operators.

Theoretically this construction is correct, but as the LIKE pattern is
probably longer than the contents of the field you are operating on,
it won't work in the way you intend it to.

> how do I get a list of scholarships based one which student selected
> it where the selected result is a string of colon seperated options?

I'm not quite sure what you mean here, but in order to be able to
match scholarships selected to an individual student given your table
structure, you could try turning the fields round, e.g.

WHERE schols_selected LIKE '%' || short_name || '%'

However this is a lousy way of doing things and will cause performance
problems with a non-trivial amount of data. (An additional table is
what would would help you here).

HTH

Ian Barwick