Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news-FFM2.ecrc.net!nntp.abs.net!newsfeed.mathworks.com!wn11feed!worldnet.att.net!attbi_feed3!attbi_feed4!attbi.com!attbi_s04.POSTED!not-for-mail From: "Marshall Spight" Newsgroups: comp.databases.theory References: <28a69e7c.0402011530.2f493fc1@posting.google.com> Subject: Re: Many-to-many query question Lines: 44 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: NNTP-Posting-Host: 24.5.186.86 X-Complaints-To: abuse@comcast.net X-Trace: attbi_s04 1075696593 24.5.186.86 (Mon, 02 Feb 2004 04:36:33 GMT) NNTP-Posting-Date: Mon, 02 Feb 2004 04:36:33 GMT Organization: Comcast Online Date: Mon, 02 Feb 2004 04:36:33 GMT Xref: newssvr20.news.prodigy.com comp.databases.theory:23717 "Jonck van der Kogel" wrote in message news:28a69e7c.0402011530.2f493fc1@posting.google.com... > I have a question on a query involving a many-to-many relationship > that I can't seem to figure out, I was hoping that one of you might be > willing to lend me a hand with this. [...] Two comments: > And then the joining table: > > join_id | book_id | author_id > 1 | 1 | 1 > 2 | 1 | 2 [...] You don't need (or want) a "join_id" here. The property of this table that you care about is that it links books and authors in a many-to-many relationship, which means that its primary key is (book_id, author_id) and having that as the primary key means that the column join_id is redundant. > Now let's say I want to query for all the authors of book #1. > Naturally this is very simple to do, but every way I think of returns > two rows, whereas what I am looking for is to get this information in > a single row. Actually, I'm pretty sure that that's *not* what you're looking for. The result as two rows is the exactly right answer. This is one of those cases where you have a prejudgement of what the solution to the problem looks like. In this case, the prejudgement excludes the right answer to the problem, so you're stuck. Probably what you need to do is work through why you think you need the answer in that specific form, and doing so will cause you to discard the prejudgement. It might help if you consider that SQL "tables" approximate sets, and "rows" are really elements of the set. There's nothing actually horizontal about rows or vertical about columns. Marshall