Home arrow MySQL arrow Page 4 - SQL In Simple English Part 2/2
MYSQL

SQL In Simple English Part 2/2


In part one of this article, Kiran looked at the basics of SQL including SELECT, INSERT, UPDATE and DELETE queries. In this, the final part of the article, Kiran's going to teach us about joins and sub queries. After reading both part 1 and 2 of this article, you should be able to construct intermediate SQL queries to extract and manipulate data from both single and multiple tables.

Author Info:
By: Kiran Pai
Rating: 5 stars5 stars5 stars5 stars5 stars / 7
May 11, 2002
TABLE OF CONTENTS:
  1. · SQL In Simple English Part 2/2
  2. · What is a join?
  3. · Inner Joins
  4. · Sub Queries
  5. · Conclusion

print this article
SEARCH DEVARTICLES

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.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials