> I want to generate a list of books that are in lib.books that doesn't
> have any subjects assigned to it.


There's a couple ways to do this.  I'm a little rusty on MySQL, so this
will just be a bit of a generic SQL, you may have to adapt.

The always popular left outer join with a null.  Outer joins will join
tables even when there isn't a matching element in one the tables. So...in
our fictional example: (LibBooks has the book ids, and BooksSubjects has
the relation of books to subject ids)

SELECT *
FROM LibBooks LEFT OUTER JOIN BooksSubjects ON (LibBoooks.BookId = LibSubjects.BookId)
WHERE LibSubjects.BookId IS NULL.

The left outer join here will still "join" a BookId and row to the
BookSubjects table if there is no match, but all those tuples that would
come from the table on the "right" will be set to null.  Hence, it only
gets those books that are always in LibBooks.

You can do a not in sub-query (syntax here might vary between SQL
servers)...soemthing like

SELECT *
FROM LibBooks
Where LibBooks.BookId NOT IN (SELECT DISTINCT BookId FROM LibSubjects)


There's a few other methods, but I can't remember them off the top of my
head.


Jonathan T. Gorman
Research Information Specialist
University of Illinois at Champaign-Urbana
216 Main Library - MC522
1408 West Gregory Drive
Urbana, IL 61801
Phone: (217) 244-4688


On Thu, 25 Jan 2007, Ken Irwin wrote:

> Hi folks,
>
> I'm trying to put together a MySQL query to do something I don't know
> how to do: get a list of materials that DON'T show up in a relational table.
>
> For example, 3 tables:
>
> 1) lib.books : lots of bib data including book_id
> 2) lib.subjects: subj_code, selector, subject_name
> 3) relational: lists book_id & subj_code
>
>
> I could do this with 2 queries, but it gets unwieldy: get a list of
> distinct book_ids and AND/NOT them all together like:
> SELECT * FROM books WHERE book_id != '4' and book_id != '7'...
> That works on really small sets, but I don't want to go that route.
>
> Is there a savvy way to structure this MySQL query. I don't even know
> the language to use to look for this information.
>
> Thanks for any help you can provide!
> Ken
>
> --
> Ken Irwin
> Reference Librarian
> Thomas Library, Wittenberg University
>