Re: question on SELECT using LIKE

Ajay,

I think line 3, 'WHERE t2.namelist LIKE %t1.name%;',
is asking postgres to find a table called '%t1' and
column called 'name%' instead of a variable between 2
wildcards.

I could be wrong. I'm sure I'll be corrected if I am!
;-)

I'm away from work and can't test this; but try
replacing line 3 with:

WHERE t2.namelist LIKE '%'||t1.name||'%';

The || is used to concatenate strings.

On a separate note, have you thought about
restructuring Table2 so that one name is matched with
one group in each row? This would result in multiple
rows for group values and multiple rows for name
values; so the primary key would consist of both the
group and name columns. Whereas the structure may
seem a little more complicated, SQL select and update
queries would be much simpler.

Best of luck,

Andrew Gould

--- "Aggarwal , Ajay" <ajay(at)crossbeamsys(dot)com> wrote:
> Please respond to ajay(at)crossbeamsys(dot)com since I
> don't subscribe to the
> mailing list.
>
> I have 2 tables :
>
> Table1
> Name Age
> joe 23
> mark 25
> linda 22
>
> Table2
> NameList Group
> joe group1
> mark group2
> joe linda group3
>
> For each person in Table1, I want to list the groups
> that he/she is a member
> of. Table2 has the group membership information,
> but notice that the first
> column of Table2 is a list of names rather than a
> single name.
>
> I want to be able to use the the following SELECT
> call :
>
> SELECT t1.name, t2.group
> FROM table1 t1, table2 t2
> WHERE t2.namelist LIKE %t1.name%;
>
> But its not working. The problem is in '%t1.name%',
> the second operand of
> LIKE.
>
> Thanks in advance. Please Cc your reply to
> ajay(at)crossbeamsys(dot)com as well.
>
> Ajay

__________________________________________________
Do You Yahoo!?
Thousands of Stores. Millions of Products. All in one Place.
http://shopping.yahoo.com/