Thread: query from a list of ids

Hi,

Here is my first table:

Table1

name| ids
-------------------------
Peter| 2, 3, 4, 5
Jack| 100, 34, 3

Both name and ids are in text format.

Here is my second table

Table2

id | Flag | Title
---------------------
2 | Red    | good
3 | Blue   | poor
4 | Green| middle

id is in integer (serial) format.

I would like to list all the rows in table 2 where the id is in the
ids field of peter. So I did

select * from tables where id in (select ids from table1 where
name='Peter')

It did not work. How can I do the query?

Thanks,

ff

am  Tue, dem 24.04.2007, um 14:19:05 -0700 mailte finecur folgendes:
> Hi,
>
> Here is my first table:
>
> Table1
>
> name| ids
> -------------------------
> Peter| 2, 3, 4, 5
> Jack| 100, 34, 3
>
> Both name and ids are in text format.
>
> Here is my second table
>
> Table2
>
> id | Flag | Title
> ---------------------
> 2 | Red    | good
> 3 | Blue   | poor
> 4 | Green| middle
>
> id is in integer (serial) format.
>
> I would like to list all the rows in table 2 where the id is in the
> ids field of peter. So I did
>
> select * from tables where id in (select ids from table1 where
> name='Peter')
>
> It did not work. How can I do the query?

Broken design, you should normalise your schema.


Okay, let me try a solution:

First, create a function:

create or replace function ids(text) returns setof t2 as $$
declare
        _ids    text;
        _sql    text;
        _rec    record;
begin
        select into _ids ids from t1 where name = $1;
        _sql := 'select * from t2 where id in (' || coalesce(_ids,'NULL') || ');';
        for _rec in execute _sql loop
                return next _rec;
        end loop;
end;
$$ language plpgsql;


Hint: the coalesce avoid errors if there are no result.


Okay, we have 2 tables:

test=*# select * from t1;
 name  |    ids
-------+------------
 Peter | 2, 3, 4, 5
 Jack  | 100, 34, 3
(2 rows)

test=*# select * from t2;
 id | flag  | title
----+-------+--------
  2 | Red   | good
  3 | Blue  | poo
  4 | Green | middle
(3 rows)

test=*# select * from ids('Peter');
 id | flag  | title
----+-------+--------
  2 | Red   | good
  3 | Blue  | poo
  4 | Green | middle
(3 rows)

test=*# select * from ids('Jack');
 id | flag | title
----+------+-------
  3 | Blue | poo
(1 row)

test=*# select * from ids('nobody');
 id | flag | title
----+------+-------
(0 rows)



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

>> name| ids
>> -------------------------
>> Peter| 2, 3, 4, 5
>> Jack| 100, 34, 3
>>
>> Both name and ids are in text format.

    IF you really do not want to use a link table (user_id, flag_id), you
could use an array of ints instead of a string...

    You can try this one.

        SELECT
            table2.*
        FROM
            (SELECT string_to_array(ids, ', ') FROM table1 WHERE name =
'Peter') AS a(a),
            (SELECT generate_series(1,array_upper(string_to_array(ids,
', '),1)+1,1)FROM table1 WHERE name = 'Peter') c(n),
             table2
        WHERE
            table2.id = a[c.n]

finecur wrote:
> Hi,
>
> Here is my first table:
>
> Table1
>
> name| ids
> -------------------------
> Peter| 2, 3, 4, 5
> Jack| 100, 34, 3
>
> Both name and ids are in text format.
>
> Here is my second table
>
> Table2
>
> id | Flag | Title
> ---------------------
> 2 | Red    | good
> 3 | Blue   | poor
> 4 | Green| middle
>
> id is in integer (serial) format.
>
> I would like to list all the rows in table 2 where the id is in the
> ids field of peter. So I did
>
> select * from tables where id in (select ids from table1 where
> name='Peter')
>
> It did not work. How can I do the query?
>
> Thanks,
>
> ff
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>