 |
 |
> 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 >