Postgres Pro
Downloads
Home   >   mailing lists

Thread: question on SELECT using LIKE

question on SELECT using LIKE

From
"Aggarwal , Ajay"
Date:
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

Re: question on SELECT using LIKE

From
Andrew Gould
Date:
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/

RE: question on SELECT using LIKE

From
"Will Fitzgerald"
Date:
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

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Aggarwal , Ajay
> Sent: Thursday, November 02, 2000 5:32 PM
> To: 'pgsql-general@postgresql.org'
> 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
> 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
>

Re: question on SELECT using LIKE

From
"Giovanni Serrato Castillo"
Date:
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
> 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
>