SQL Subqueries
Subject:   sub query help
Date:   2006-08-25 14:39:28
From:   fperez
HI- I'm having trouble getting the results I need from an sql query. I believe I can accomplish my task with a subquery however I can't get it to work. I have a table of book titles and a one to many table of books read (that holds many people and the book they read). Now I need to get a list of books left to read by a particular person from my join. So the query starts with a join between the books table and the books read table to get the book titles. I use a left join to get all the book titles even if they haven't been read. However I need it to filter out the books that the particular person has read and leave me with a list of books left to read by the particular person.

please advise-frank

Full Threads Oldest First

Showing messages 1 through 1 of 1.

  • sub query help
    2006-11-17 21:08:48  pfindley [View]

    It's been a long time since August 25th when you posted your question, but i just saw it last night and thought i would give it a try. I created a sample db called "bks" with 3 tables in it:
    1) Books (1 field called 'books')
    2) BooksRead (2 fields called 'rdr' and 'bk')
    3) Readers (1 field called 'reader')
    For my example, i included 7 book titles in table 'Books', 3 student's names in table 'Readers'. 'BooksRead' is a table that includes student's names from 'Readers' along with books they have read from 'Books'.

    Here is the data in Books:
    Mere Christianity
    God In the Docks
    The Great Divorce
    The Problem of Pain
    The Screwtape Letters
    The Weight of Glory

    Here is the data in BooksRead:
    Student1 Mere Christianity
    Student1 Miracles
    Student2 God In the Docks
    Student2 The Problem of Pain
    Student2 The Screwtape Letters
    Student1 The Weight of Glory
    Student1 The Screwtape Letters
    Student3 The Screwtape Letters
    Student3 Mere Christianity

    Here is the query that will return a list of the books each student has NOT read:

    SELECT CONCAT( reader, books ) , reader, books
    FROM Books, Readers
    WHERE CONCAT( reader, books ) NOT IN
    SELECT CONCAT( ` rdr` , `bk` ) FROM BooksRead
    ORDER BY reader;

    There are probably easier ways to do this, but this was the way i was able to figure it out. Here is an explanation:

    SELECT CONCAT( reader, books ) , reader, books
    FROM Books, Readers

    First thing i did was join and concatonate a list of all the readers with all the books. This may not be such a great option depending on how many books/students you have. This basically produces a list of ALL the readers, alongside ALL the students. Then it compares this list to a list the following code produces:

    WHERE CONCAT( reader, books ) NOT
    IN (
    SELECT CONCAT( ` rdr` , `bk` )
    FROM BooksRead

    Anywhere there is NOT a match in the two lists it includes that data, but if there is a match of the first list to this second list, it does not include that data.
    Lastly, the following simply puts it in order by reader's name:

    ORDER BY reader;

    Something tells me you probably figured that last part out already... perhaps by now, you've figured all of this out and in a much better way. If so, would you mind sharing your knowledge with me on how you did it? I'd appreciate learning from you.
    Hope his was helpful.
    God bless,
    paige findley