>>> On Thu, Jan 25, 2007 at 3:48 PM, Ken Irwin <[log in to unmask]> 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 want to generate a list of books that are in lib.books that doesn't > have any subjects assigned to it. > > 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 I'm assuming it's a recent version of MySQL, so you should be able to do a subquery: SELECT * FROM books WHERE book_id NOT IN (SELECT book_id FROM relational); (of course, SELECT * is a recipe for trouble in code should your schema ever change... but that's a different subject). Dan -- Systems Librarian, Bibliothèque J.N. Desmarais Library Laurentian University / Université Laurentienne Phone: 705-675-1151 x3315