Udjuni.com

Mathematics

Science

Computing

Accounting

Business

History

Agriculture

Biology

Selecting data from multiple tables

So far we have covered methods of retrieving data from single table. In real life however, you hardly get to only retrieve data from one table. In other words, to retrieve and interpret information, you normally get such information from different tables each of which will contain only information about one and only one entity.

Earlier in the postings titled "Database design", we covered specifics on how to create tables and retrieve data from a single table. Before we get to retrieving data from multiple tables, we will create another table and then demonstrate why and how you can draw information from different tables in order to make sense of a situation.

Note again that I will be using MySQL syntax here, but the concept is generally the same regardless of the Relational database System that you choose.

Create table product (

	productid bigint primary key auto_increment,

	productname varchar(100),

	unitprice numeric(8,2),

	availableqty int);

Here is what we have so far we have the customer table that we created in the previous postings, and we just created ourselves a new table in which we shall store all the information about every product we have in stock.

Now, before we go any farther; lets us first discuss why and how we are going to use data from multiple tables. Assuming that we run a small retail store. We stock it with products that we have established our customers would want to buy. In simple terms, for us to really be able to run a profitable business; we need to know who is buying what products and how often they buy.

The business scenario

So far we have the capacity to record every detail about our customer (using the customer table). We also have the capacity to store every detail about the items we have in stock (using the product table). One thing is missing, we need to keep track of the daily activities such as which customer bought which product and in what quantities.

Why do we need to do that? Because if we knew and recorded our customers' buying patterns, we would use the information to figure out which products are in great demand? who is buying them? what else are they buying? When are they likely to come back? This is all information that is critical to making sure that we have the products when our customers need them, and that is how our business makes money.

Creating a table to store transaction data

Here is what our transaction table would look like; and remember, the idea is to store the data in a way that minimizes duplicates:



CREATE TABLE customer_transaction  (

 transactionid bigint primary key auto_increment,

Customerid bigint references customer(customerid),

Productid bigint references product(productid),

Qty int,

Transdate datetime );

Note: here that we do not have to repeat all the details about either the customer or product tables. Every time a new record is entered, we use the customerid an productid to associate a purchased product with the buyer.

Key concepts

From the above SQL statements, you need to grasp the following concepts. Note that in the definition of the product, we used the terms Primary key and references. The term primary key denotes a field that uniquely identifies a record. This by implication means that this particular field can not have duplicates.

The phrase reference means that the values in this field must exist in the referenced field of the referenced table. This means that customerid field in customer_transaction table can never have a value that does not already exist in customerid field of the customer table. In database language, customerid in customer_transaction table is referred to as foreign key and must match the data type of the primary key of the referenced table. The rule that enforces this kind of relationship is called Referential integrity

Assuming that our tables are populated with data and now we need to know how much of what product has each of our customers purchased over a period of time. We specifically want to see the name, a list of products, quantity of each product purchased between June 1st 2009 and June 30th 2009. Here is what our query is going to look like.



       	SELECT firstname, lastname, productname, SUM(Qty) as Qty

  	FROM customer a INNER JOIN customer_transaction b on a.customerid = b.customerid

                     INNER JOIN product c on b.productid = c.productid

            GROUP BY firstname, lastname, productname

            WHERE transadate BETWEEN ‘2009-06-01’ and ‘2009-06-30’

            ORDER BY firstname, lastname;

Note that we used the keyword INNER JOIN to join the date from different tables in order to present data in a meaningful way. Also note that the column on which you join the tables must be of the same data type and have the same values. In other words, You can not join tables on fields that do not have common data

Do not worry too much about the every detail of a joined query, all you have to do at this stage is have a basic idea of how you can combine data from multiple tables in order to give a meaningful presentation of what is going on. I will be explaining the details of join statements shortly.

Creating database tables with SQL
To create a table in SQL, you use a command that requires the name of the table as well as names and data types of the fields of that table. In general, the syntax for creating a table is as follows: CREATE table_name ( column_name data_type); The table nam

Multiple Signs

SQL SELECT Queries
So, we have created your database, added data into the tables and now what? In real world nobody collects data for the sake of collecting something. It has to be something that they can use at a later time. This section is dedicated to retrieving information stored in a da

Joining a table with itself (SQL self-join)
Joining a table with itself is sometimes the only way to make sense of information. Consider a scenario where everybody's information in a company including managers and ordinary employees. That same table contains information on who reports to who?

Copying data from one table to the other
here are several ways to copy or move data from one table to another. Some methods are applicable when copying data from one table to another on the same database and other methods are applicable for copying data from one database to another and even or a different server. Bellow are several methods

Common SQL Data types
Now that you have identified your data entities or tables, you have to define each attribute of the tables you have identified. These definitions include the name of the attribute and the type of data that can be stored in the field. Most relational database systems have a number of data types that

Database Design
After gathering and analyzing your requirements, and depending on whether or not your application needs a database to store information; database design is the next big thing. This comes even before you start coding the application program. The reason for this is that, the application program

Updating and deleting data with SQL
In the posting “Introduction to SQL”, we covered the creation of data storage space or database and its entities. We then went a little further to cover a methods of entering data into those entities or tables and methods of retrieval. There are however, times when the data stored in

The state of Periodic table elements
- In addition to the group 8 or O elements, the following elements also naturally occur in gas state: H, N, O, F, Cl and Br -Among metals and non-metals, the following occurs in liquid state: Na, K, Rb, Cs, Fr, Hg a

compound interest heats of reaction

Udjuni.com

Selecting data from multiple tables

The business scenario

Creating a table to store transaction data

Key concepts

RELATED ARTICLES: