logo
g Text Version
Beauty & Self
Books & Music
Career
Computers
Education
Family
Food & Wine
Health & Fitness
Hobbies & Crafts
Home & Garden
Money
News & Politics
Relationships
Religion & Spirituality
Sports
Travel & Culture
TV & Movies

dailyclick
Bored? Games!
Nutrition
Postcards
Take a Quiz
Rate My Photo

new
European Travel
Action Movies
Bible Basics
Houseplants
Romance Movies
Creativity
Family Travel


dailyclick
All times in EST

Autism Spectrum Disorders: 4:00 PM

Full Schedule
g
g ASP Site

BellaOnline's ASP Editor

g

Joining Tables in SQL


Do you have two tables, and want to be able to calculate totals and merge information across them? Just join them together!

This isn't even an ASP command, it's just regular SQL. Let's say you have two table, one with your sale items in it and another with your purchase information. You want to do a report to see how many of each item you've sold. So let's assume you have:

TABLE: items
Rows: item_id, item_name, item_desc

TABLE: purchase_detail
Rows: purch_orderid, purch_itemid, purch_qty

So now you want to do a select that returns one row for each item_name, with the total count of the items it found in the purchase detail. So your select statement would read:

SELECT i.item_name, sum(p.purch_qty) from
items i, purchase_detail p where i.item_id = p.purch_itemid
group by i.item_name order by i.item_name;

That just kicks out a list for you, alphabetically, of each item and the total quantity of it ordered. You can of course add on a where statement if you want, to select only orders from a certain date range or other fields you may have.

There's a downside to this query. If there isn't any record for an item being purchased, it won't show up on the list. So you might not realize it had a zero order count, because it's not even there. To force a row to show up if it's in one table but not the other, you need an OUTER JOIN. This is basic set theory from high school - it's a join where you get everything from one set even if there isn't a match from the other set. To write an outer join, do:

SELECT i.item_name, sum(p.purch_qty) from
items i, purchase_detail p where i.item_id *= p.purch_itemid
group by i.item_name order by i.item_name;

The star (*) indicates that you must show every single row in that first table, even if there isn't a matching row in the second table.

Introduction to ASP Ebook

Download this ebook to get everything you need to know about learning ASP - from a step by step tutorial to function lists, sample code, common errors and solutions, and much more! 101 pages.
Add Joining+Tables+in+SQL to Twitter Add Joining+Tables+in+SQL to Facebook Add Joining+Tables+in+SQL to MySpace Add Joining+Tables+in+SQL to Del.icio.us Digg Joining+Tables+in+SQL Add Joining+Tables+in+SQL to Yahoo My Web Add Joining+Tables+in+SQL to Google Bookmarks Add Joining+Tables+in+SQL to Stumbleupon Add Joining+Tables+in+SQL to Reddit




RSS | Related Articles | Editor's Picks Articles | Top Ten Articles | Previous Features | Site Map


For FREE email updates, subscribe to the ASP Newsletter


Past Issues


print
Printer Friendly
bookmark
Bookmark
tell friend
Tell a Friend
forum
Forum
email
Email Editor


Content copyright © 2014 by Lisa Shea. All rights reserved.
This content was written by Lisa Shea. If you wish to use this content in any manner, you need written permission. Contact Lisa Shea for details.

g


g features
Archives | Site Map

forum
Forum
email
Contact

Past Issues
memberscenter


vote
Poetry
Daily
Weekly
Monthly
Less than Monthly



BellaOnline on Facebook
g


| About BellaOnline | Privacy Policy | Advertising | Become an Editor |
Website copyright © 2014 Minerva WebWorks LLC. All rights reserved.


BellaOnline Editor