Amazon.com: PC Books || Educational Software || Magazines
Amazon: Books-CA || Software-CA || Books-UK || Software-UK

HTML | Javascript | ASP | PHP | VBScript | SQL | Hardware | PC FAQ| WinXP|

Learn to Build, Upgrade, or Repair your Computer Ebook + PC Safety 101 kwwebservice.com Reasonably priced web development & hosting
SQL Basics
Introduction
Create Tables
Data Type
Delete/Modify Table
Add Rows
Create Sequence
Quiries
Single-Row Function
Group Functions
Joints
Create Views
Books @
CA Amazon
UK Amazon
US Amazon

  :: Home
PC Topics
  :: Build A PC
  :: Windows XP
  :: PC Help
Tutorials
  :: HTML
  :: JavaScript
  :: ASP
  :: PHP
  :: VBScript
  :: SQL
Miscellaneous
  :: Code/Scripts
  :: Forum
  :: Links
  :: Contact us
  :: Tell A Friend
  :: In Somali
  ::
Web Articles
  Top Web Hosting
Reviews
  Processing
Online Card Payment Guide
  Domain name
registration & buying guide
  Getting
a website online guide
  Search Engine
submission & optimization tips
Netfirms Web Hosting
Free trial
Quality business correspondence.
Yahoo! Search Marketing

Joints

Using SQL Joints, you can retrieve data more than one table or view using the keys [primary & foreign] references.
The syntax for joining multiple tables is as follows:
SELECT column, column..
FROM table1, table2
WHERE table1.join_column=table2.join_column;

The SELECT statement contains the columns to retrieve and may came from two or more tables.  If the selected column exists both tables, specify the table [table.column].  You have to specify the tables in the FROM clause and the joint condition is done in the WHERE clause.  In the WHERE clause, you need the table name and dot followed by column name.  The column name in the WHERE clause is the joint column [keys].  AND & OR also normally used to make multiple joint conditions.  There are about four basic types of joints, Equality Joints, Outer Joints, Self Joints, & Inequaligyt Joints.

Equality Joints

Equality Joints happens when two tables are joined based on values in one table being equal to values in another table.  For example, product table and inventory table may have joint column of product_id as it show in the following tables.
Product Table
Product IDNameDescriptionPriceCost
100000000PrinterInkjet 300 colour Printer12080
100000001Printer1220CXI Inkjet Printer 200130
100000002PrinterPhoto 890 Injet Printer 250200
100000003PrinterPhoto 890 Injet Printer 300270
Inventory Table
Product IDqty_on_handqty_on_ordermin_reqmx_req
100000000200 1025
100000001105 215
100000002210 112
100000003115 115

The query to perform equality joints might loook like this:
SELECT product.product_id,name,price,qty_on_hand, qty_on_order
FROM product, inventory
WHERE product.product_id=inventory.product_id;

Product table is specified in the SELECT statement to issue th product_id and the reason is, product_id exists both tables and if you don't specified which table to select from, you will receive ambiguous error.  The query will select all the selected rows from both tables since there is always product_id equal to product_id in the other table.

Outer Joints

Outer Joint is joint condition where all the rows of a table are selected along with their matching rows in the other table.  For example, you might want select all your customers along with their orders if they have orders.  + sign is used in the WHERE clause beside the child table within parentheses.  The following is SQL joint statement to select every customer in the customer table along with their orders if they have orders and if they don't an order, it will select blanks.
SELECT customer.customer_id, firstName, lastName, item_id,qty_ordered,price
FROM customer, order
WHERE customer.customer_id=order.customer_id(+);

Self Joints

Self Joint is joint of a table by it self.  For example, if you want retrieve customers whom ordered same products twice or more assuming there is num_order column that keeps track the number of orders customers made.  Here is how you would do this using a self joints:
SELECT o1.customer_id
FROM order o1, order o2
WHERE o1.item_id=o2.item_id AND o1.num_order>1;

This query simply created two table alias, o1 and o2 which represents two copies of the table order then compares if item_id exists both table when order is placed two or more times by a customer.

Inequality Joints

Inequality joint is when each record in one table is joined with every record in the second table using operators <>, <, >, >=, etc to define an inequality condition.  It's opposite of inner joints.  This type of joint is rarely used since joint columns are keys & inequality comparison of the keys has no meaningful applications.
Here is an example:
SELECT firstName ||' '||lastName "Full Name"
FROM customer, order
WHERE customer.customer_id < order.customer_id;
Group Functions Creating Views
Listed @ ConsumerVote.com - The Consumer Rated Web Directory
PC Articles
  Computer Safety
prevent viruses & Spyware