SQL query help - online music shop - labels & styles

From: Oliver Beddows <oliver-b(at)ntlworld(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: SQL query help - online music shop - labels & styles
Date: 2001-05-18 12:20:25
Message-ID: 01051813202504.04989@linux
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)ntlworld(dot)com

(Nearly forgot - I`m using PostgreSQL v.7.0.3, and SuSE linux 7.1)

Browse pgsql-novice by date

  From Date Subject
Next Message Nabil Sayegh 2001-05-18 13:00:45 Re: SQL query help - online music shop - labels & styles
Previous Message toto 2001-05-18 11:40:56 plpgsql help