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