Home arrow Site Administration arrow Page 6 - Database Essentials

Joined At The Hip - Administration

Are you new to the wonderful world of databases? Confused by thesudden flood of technical jargon? Don't know the difference between a"trigger" and a "stored procedure", a "join" and a "subquery"? Look nofurther - the solution is right here!

TABLE OF CONTENTS:
  1. Database Essentials
  2. The Customer Is King
  3. Relationships
  4. Invasion Of The Foreign Keys
  5. Looking Up The Index
  6. Joined At The Hip
  7. Room With A View
  8. Pulling The Trigger
  9. So That's Where All My Money Went...
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 7
February 26, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
Most databases also allow you to query two or more tables at a time, and display a combined set of results. This is technically referred to as a "join", since it involves "joining" different tables at specific points to create new views of the data.

Here's an example of a simple join:

sql> SELECT names.CustomerID, FirstName, LastName, StreetAddress, City, State, Zip, Country FROM names, addresses WHERE names.CustomerID = addresses.CustomerID;
+------------+-----------+----------+------------------------------+---------------+-------+--------+---------+
| CustomerID | FirstName | LastName | StreetAddress                | City          | State | Zip    | Country |
+------------+-----------+----------+------------------------------+---------------+-------+--------+---------+
|     234673 | John      | Doe      | 12, Some Street              | Nowheresville | ZZ    | 748202 |      56 |
|     734736 | Julius    | Caesar   | The Palace Behind The Lake   |Ancient Rome   | NA    |      0 |       0 |
|       1243 | Daffy     | Duck     | Next Door To The Rabbit Hole |    Toontown   | TT    |  98765 |       0 |
+------------+-----------+----------+------------------------------+---------------+-------+--------+---------+


In this case, the "names" and "addresses" tables have been joined together through the common column "CustomerID".

And you can also nest one query within another, such that the result of the inner query provides data for the outer query. Such a query is referred to as a "subquery", and it allows a great deal of flexibility when formulating long and complex queries.

Let's suppose you wanted to find out the names and addresses of all customers with an account balance greater than $1000.00

sql> SELECT FirstName, LastName, StreetAddress, City, State, Zip, Country FROM names, addresses WHERE names.CustomerID = addresses.CustomerID AND names.CustomerID IN (SELECT CustomerID FROM balance WHERE AccountBalance >= 1000);

In this case, SQL will first execute the inner query

sql> SELECT CustomerID FROM balance WHERE AccountBalance >= 1000;+------------+| CustomerID |+------------+| 734736 || 234673 |+------------+


and then assign the return value(s) to the outer query, which will display the result.

sql>SELECT FirstName, LastName, StreetAddress, City, State, Zip, Country FROM names, addresses WHERE names.CustomerID = addresses.CustomerID AND names.CustomerID IN ('734736', '234673'); +-----------+----------+----------------------------+---------------+-------+--------+---------+ | FirstName | LastName | StreetAddress | City | State | Zip | Country | +-----------+----------+----------------------------+---------------+-------+--------+---------+ | John | Doe | 12, Some Street | Nowheresville | ZZ | 748202 | 56 | | Julius | Caesar | The Palace Behind The Lake | Ancient Rome | NA | 0 | 0 | +-----------+----------+----------------------------+---------------+-------+--------+---------+

There is a limit on the number of sub-queries you can use in a single SQL statement, but it's usually quite a comfortable number.

 
 
>>> More Site Administration Articles          >>> More By icarus, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

SITE ADMINISTRATION ARTICLES

- Coding: Not Just for Developers
- To Support or Not Support IE?
- Administration: Networking OSX and Win 7
- DotNetNuke Gets Social
- Integrating MailChimp with Joomla: Creating ...
- Integrating MailChimp with Joomla: List Mana...
- Integrating MailChimp with Joomla: Building ...
- Integrating MailChimp with Joomla
- More Top WordPress Plugins for Social Media
- Optimizing Security: SSH Public Key Authenti...
- Patches and Rejects in Software Configuratio...
- Configuring a CVS Server
- Managing Code and Teams for Cross-Platform S...
- Software Configuration Management
- Back Up a Joomla Site with Akeeba Backup

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: