Women in Technology

Hear us Roar



Article:
  SQL Subqueries
Subject:   sub query help
Date:   2006-11-17 21:08:48
From:   pfindley
Response to: sub query help

Frank,
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
Miracles
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