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@crossbeamsys.com> wrote:

Please respond to ajay@crossbeamsys.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@crossbeamsys.com as well.

Ajay

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

The thing is, this is the wrong thing to do. The relational model makes it
easy to define 'lists' or 'sets.' Table 2 should have:

table2
Name group
joe group1
mark group2
joe group3
linda group3

The table is now just what you want: it shows the groups each person is a
member of. So:

SELECT name, group FROM table2 ORDER BY name;

Will

Prueba lo siguiente:

SELECT t1.name, t2.group
FROM table1 t1, table2 t2
WHERE t2.namelist LIKE '%' || to_char(t1.name) || '%';

----- Original Message -----
From: "Aggarwal , Ajay" <ajay@crossbeamsys.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, November 02, 2000 5:32 PM
Subject: [GENERAL] question on SELECT using LIKE

Please respond to ajay@crossbeamsys.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