>>> On Thu, Jan 25, 2007 at 3:48 PM, Ken Irwin <kirwin_at_WITTENBERG.EDU> 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
Received on Thu Jan 25 2007 - 15:12:41 EST