hello,
I`m currently developing an online music shop, mainly as a learning
experience. And I`m having a few problems regarding, querying and displaying
records labels and their associated styles of music.
Say I have the following tables and sequences :
-------------------------------
create table label
(id integer not null,
name text not null,
primary key(id));
create sequence label_id_seq increment 1 start 1;
create table label_style
(label_id integer not null,
style_id smallint not null);
create sequence style_id_seq increment 1 start 1;
create table style
(id smallint not null,
name text not null,
primary key(id));
I then insert a some styles of music:
------------------------
insert into style values(nextval('style_id_seq'),'Trance');
insert into style values(nextval('style_id_seq'),'Techno');
insert into style values(nextval('style_id_seq'),'House');
I insert a record label:
--------------
insert into label values(nextval('label_id_seq'),'Matsuri Productions');
I associate the some styles of music to that label:
--------------------------------
insert into label_style values(1,1);
insert into label_style values(1,2);
insert into label_style values(1,3);
Now I join all three tables like so:
----------------------
SELECT l.name as label,s.name as style
FROM label l, style s, label_style ls
WHERE s.id = ls.style_id
AND l.id = ls.label_id
Which produces the following:
----------------------
label | style
----------------------
Matsuri Productions | House
Matsuri Productions | Techno
Matsuri Productions | Trance
BUT! How can I achieve the following??
--------------------------------
label | style1 | style2 | style3
-------------------------------
Matsuri Productions | House | Techno | Trance
What kind of query do I need to use? Am I dreaming here or what?
Any help with this would be GREATLY appreciated.
oliver-b@ntlworld.com
(Nearly forgot - I`m using PostgreSQL v.7.0.3, and SuSE linux 7.1)