Thread: how to do this query
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. 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? thx, -wj
2010/1/9 Wes James <comptekki@gmail.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
How about schols_selected like '%' || short_name || '%' > 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. > 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? > thx, > -wj > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > Здесь спама нет http://mail.yandex.ru/nospam/sign
> schols_seleced like '%' || short_name || '%' doesn't make sense to me select 'Ya-Ya' from (select 'schol1:schol2:schol3' as schols_selects ) AS X where schols_selects like '%schol2%' > If you look at the contents of these fields short name containts > "schol1", schols_selects containts "schol1:schol2:schol3" so > schols_seleced like '%' || short_name || '%' doesn't make sense to me > since the like operator is trying to find text1 in text two with %% > right? > 2010/1/9 msi77 <msi77@yandex.ru>: > > Развернуть > > How about > > > > schols_selected like '%' || short_name || '%' > > > Развернуть > > > 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. > > > 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? > > > thx, > > > -wj > > > -- > > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > > To make changes to your subscription: > > > http://www.postgresql.org/mailpref/pgsql-sql > > Здесь спама нет http://mail.yandex.ru/nospam/sign > Здесь спама нет http://mail.yandex.ru/nospam/sign