Help with query

[keegan]

I have a table of members, and another table of their preferences. The preferences table can contain anywhere from zero to ten rows for each member row.

Is it possible in one query to select information about each member, and get a list of the member's preferences in the same row? They don't have to be returned separately - they could be in a comma-separated list, for example.

So what I would want returned is a list of rows like this:

|------------------------------------|
| member_id | name | preference_list |
| 12 | Joe | 3,8,12,4,23 |
|------------------------------------|

Is this possible, or will I have to do a separate query for each member?

Thanks,
Keegan

[Anon]

Have your fun ...

test1=> create table t2 (id int, n varchar(64), v varchar(64));
CREATE
test1=> insert into t2 values (1, 'a', 'A');
INSERT 263829 1
test1=> insert into t2 values (1, 'c', 'C');
INSERT 263830 1
test1=> insert into t2 values (1, 'b', 'B');
INSERT 263831 1
test1=> select * from t2;
id | n | v
----+---+---
1 | a | A
1 | c | C
1 | b | B
(3 rows)

test1=> create table t1 (id int, s varchar(64));
CREATE
test1=> insert into t1 values (1, 'Hello');
INSERT 263842 1
test1=> select t1.s, a.v, b.v, c.v from t1, t2 a, t2 b, t2 c where t1.id=1 and (a.id = t1.id and a.n = 'a') and (b.id = t1.id and b.n='b') and (c.id = t1.id and c.n='c');
s | v | v | v
-------+---+---+---
Hello | A | B | C
(1 row)

[keegan]

Thanks, but this is something that needs to be done "on the fly" to be used by a PHP script. I think making a temporary table might be the answer... but I don't know how to insert the data in the correct order in that table.

Keegan

[Anon]

It is a simple matter of generating the SQL and then executing it. there is no need to bother with temporary tables, which will likely end up providing you with more complexity and slower performance.

It's up to you, of course.