Re: Many-to-Many relation
От | Mukta A. Telang |
---|---|
Тема | Re: Many-to-Many relation |
Дата | |
Msg-id | Pine.SGI.3.94.1030527124239.13353A-100000@darya.nio.org обсуждение исходный текст |
Ответ на | Re: Many-to-Many relation (Peter Childs <blue.dragon@blueyonder.co.uk>) |
Ответы |
Re: Many-to-Many relation
(Bruno Wolff III <bruno@wolff.to>)
|
Список | pgsql-sql |
On Mon, 26 May 2003, Peter Childs wrote: > On Mon, 26 May 2003, Mukta Telang wrote: > > > Hi, > > > > I am designing a database of paper publications. > > > > A paper has one or more authors and an author has one or more papers. > > In this way there is many-to-many relation. > > > > An author of a paper has a "level", that is an author of a paper of > > level one is the main author of the paper and the author > > with level two is the second author of the paper and so on. > > > > So we have following tables: > > 1. author > > 2. paper > > 3. author_paper > > > > The attributes of author are: > > 1. author_id > > 2. name > > > > The attributes of paper are: > > 1. paper_id > > 2. journal > > 3. year > > 4. volume > > 5. issue > > > > The attributes of author_paper are: > > 1. author_id > > 2. paper_id > > 3. level > > SELECT * FROM author_paper full join (author on (author_paper.author_id = > author.author_id) full join paper on (author_paper.paper_id = > paper.paper_id)) ORDER BY paper.paper_id, author_paper.level; > > I think is what you want, You will need to replace the * with the fields > you want of course! (Bung "CREATE VIEW <name> AS" to create the view) > If you don't want to list authors without papers or papers with no > authors you will need to change "full join" to "left join" and you may > need to swap the order the joins occur in if you want one but not the > other. > > Peter Childs > > > > > Now I want to create a view which displays all the information about > > every paper.That is title,year,journal,volume, issue and all the authors > > of the paper sorted according to their level. > > > > How to do this? > > > > Thanks in advance, > > > > Mukta I am not good in sql..and will have to brushup on joins. Please excuse me if I am asking something silly! suppose if I give: SELECT author_paper.paper_id, paper.title, author.name FROM author_paper full join author on (author_paper.author_id = author.author_id) full join paper on (author_paper.paper_id = paper.paper_id) ORDER BY paper.paper_id, author_paper.level I get the paper_id,title and an author of a paper. In this way for every author of the paper I get the records in above format. but what I want is paper_id, title and all the authors of the paper sorted according to their level.. How to do this? Mukta
В списке pgsql-sql по дате отправления: