SQL query help - online music shop - labels & styles
От | Oliver Beddows |
---|---|
Тема | SQL query help - online music shop - labels & styles |
Дата | |
Msg-id | 01051813202504.04989@linux обсуждение исходный текст |
Список | 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 по дате отправления: