SEO

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>> 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...

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match