Thread: Many-to-Many relation
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 authorwith 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 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
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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
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
On Tue, May 27, 2003 at 13:03:00 -0700, "Mukta A. Telang" <mukta@darya.nio.org> wrote: > > 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? This really does give you the information you want. As your reading rows you can skip the paper information as long as the paper_id doesn't change. If you really want the authors returned as part of the same row, you are probably best off simulating that in your application. Without a bound on the number of authors for a paper, you are going to need to do separate queries for each paper which will be slower than sending extra data to the application (at least in the normal case). You might be able to use something from contrib/tablefunc to do what you want if you have a bound on the number of authors per paper.