----- Original Message -----
From: "John Meyer" <[EMAIL PROTECTED]>
To: "List: MySQL" <mysql@lists.mysql.com>
Sent: Tuesday, May 30, 2006 5:09 PM
Subject: Query problem
Setup
TITLES:
TITLE_ID
AUTHORS:
AUTHOR_ID
TITLE_AUTHOR:
(TITLE_ID,AUTHOR_ID)
Problem:
Given a title, I need to find all the authors who aren't connected with
that particular book.
That's a pretty odd requirement, I must say. If your database has thousands
or millions of books, you'd have to assume that virtually ALL of the authors
in the database are NOT connected with a particular book.
I'm trying to think of a situation where that list of people who were not
connected with the book was actually useful.... Okay, maybe if you were
looking for authors who could review the book, the query you want could be
useful for identifying potential reviewers. Even if that was thousands of
authors, it's still a smaller list than the list of all human beings on
Earth :-)
In any case, the query is pretty simple assuming you are using a version of
MySQL which supports subqueries:
select AUTHOR_ID
from AUTHORS
where AUTHOR_ID not in
(select AUTHOR_ID
from TITLE_AUTHOR
where TITLE_ID = 123)
You simply plug in the title_id of the book in question in place of 123 and
you're all set.
If I were creating the database, I would have an id _and_ an author name in
the AUTHORS table and I'd have an id _and_ a title in the TITLES table. Then
I'd modify the query above to do joins so that the result showed me the
author names and searched so that I was looking for the book title, not the
book id. But I'm guessing that you already have that in mind and just
simplified the question to get the bare essence of it. Or maybe you only
have a few dozen books and will quickly memorize the author names and titles
that go with each author id and title id.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 29/05/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
