SQL In Simple English Part 2/2 - Sub Queries
(Page 4 of 5 )
Use the same tables that I have used before. In case you want to find the titles of the books written by the Jason. What would you do?? Give it a thought and try to figure some way to get it done.,. then read on.
If you've figured out a solution, then how did you do it? Did you use two queries, and if so, do you think you can get it down to one? This is where sub queries come in use. You could find the author_id for that particular author and then also find the books written by him in the same query.
Ok, here is what I am trying to do now. In English I could state it as follows - "I want a list of the books that have been written by author whose first name is Jason" . In SQL I could possibly use the following:
SELECT title FROM authors,books WHERE ( books.author_id = (SELECT author_id from authors where firstname = 'Jason') AND books.author_id = authors.author_id) Seems complex.. but its actually simple.. This is after all SQL In Simple English. The first part is simple.. you select the title of the books whenever the criteria is met. Lets analyze the criteria.
The inner most part of the query, SELECT author_id from authors where firstname = 'Jason' evaluates to the 1 (its just a simple query which you learnt right at the beginning). Thus the entire query now becomes
SELECT title FROM authors,books WHERE (books.author_id = 1 AND books.author_id = authors.author_id) Here we are using the 2 original tables: the author’s table with 2 rows, and the books table with 4 rows. Now when the joined table is created it would consist of 8 rows (2x4 as explained earlier). From these 8 rows, only 4 would contain correct information and the rest of them would be present as a result of the various combinations that were possible. Now, the books.author_id = authors.author_id part of the WHERE clause separates these 4 correct rows and discards the remaining rows. Finally, the other part of the WHERE clause books.author_id = 1 would selects the final single row from the 4 correct rows.
Thus you would get the names of the books written by the author named Jason Hunter.
Next: Conclusion >>
More MySQL Articles
More By Kiran Pai
