From: Ian Barwick <barwick@gmail.com>
To: Wes James <comptekki@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how to do this query
Date: Sat, 9 Jan 2010 18:07:28 +0900
Message-ID: <1d581afe1001090107j7ace3eedm197d12b50579be58@mail.gmail.com> (raw)
In-Reply-To: <533df7fa1001082026h53b5d897x2fc1fa2a6f7ff6df@mail.gmail.com>
References: <533df7fa1001082026h53b5d897x2fc1fa2a6f7ff6df@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: barwick@gmail.com, comptekki@gmail.com
Subject: Re: how to do this query
In-Reply-To: <1d581afe1001090107j7ace3eedm197d12b50579be58@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox