SQL query help - online music shop - labels & styles - Mailing list pgsql-novice

From Oliver Beddows
Subject SQL query help - online music shop - labels & styles
Date
Msg-id 01051813202504.04989@linux
Whole thread Raw
List pgsql-novice
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)

pgsql-novice by date:

Previous
From: "toto"
Date:
Subject: plpgsql help
Next
From: Nabil Sayegh
Date:
Subject: Re: SQL query help - online music shop - labels & styles