Learning SQL - Design Tips - Joining 'em up.

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

Joining it all up

A Relational Database holds data that are related. Saying 'data are' sounds really stupid to me, I always want to say 'data is' but I suppose we should do everything by the book. Hold on a minute! I'm writing the book so I can say what I like!

Let's start by creating a table to store Children that we can join up with the QiSQL_Parents table we created in the Basics Lesson

create table QiSQL_Children (
id integer primary key,
parent_id integer not null,
first_name text not null,
last_name text not null ) ;

Notice how we always add an Integer Primary Key, this column is used to identify the row in the database. We never write any values to this key, SQLite will automatically populate this for us.

Note also that we have added some NOT NULL keywords to our create statement, these will tell the database that a value must be entered into these columns.

The column 'people_id' is known as a FOREIGN KEY. This is the column that links to our parent table - QiSQL_Parents. So, the value we enter here will be the ID of the parent.

Introducing Data Models

A Data Model is a visual representation of our joined tables.

Data Mdoel 1

The boxes represent the tables and the line with the 'crows foot' shows that the parent table (QiSQL_Parents) may have only ONE record while the child record (QiSQLChildren) may have MANY records. For the purposes of this lesson we will assume that a child may only have one parent and that a parent may have many children. In a later lesson we will discuss how to resolve a 'many to many' link so that we can create a real world situation where children may have more than one parent!

Finding the parent

Try this SQL statement and see what happens:

insert into QiSQL_Children ( first_name, last_name ) values ( 'Peggy' , 'Brown');

If you are using a SQL Tool like QiSQl you will have an error returned - "Error Code 19", "QiSQL_Children .people_id may not be NULL". Well that makes sense, the insert statement failed as there was no value for the column Parent_ ID. Unfortunately some SQL Tools do not return errors they just ignore the statement!

So, the first thing we need to do is find the row in the QiSQL_Parents table that we want to join Peggy to. So let's select all the records from the table:

select * from QiSQL_Parents;

The returned results should read:

id first_name last_name
1 John Smith
2 Harry Jones
3 Linda Brown
4 Susan Brown

Peggy is Susan Brown's daughter and we can see that Susan's ID is 3. Let's try and insert that record again

insert into QiSQL_Children ( parent_id , first_name, last_name )
values ( 4 , 'Peggy' , 'Brown' );

Lets Have A Look

Now we have entered a child record we can do a fancy SELECT statement with a JOIN!

select QiSQL_Parents.first_name,
QiSQL_Parents.last_name ,
QiSQL_Children.first_name ,
QiSQL_Children.last_name
from QiSQL_Parents ,
QiSQL_Children
where QiSQL_Parents.id = QiSQL_Children.parent_id;

Because we have TWO tables in our FROM clause separated by a comma, we need to tell the SELECT statement which columns we mean by preceding them with the table name and a dot. If we tried to enter 'select first_name, last_name, first_name, last_name....' SQL would get very grumpy and throw us an error.

The returned data shows as:

first_name last_name first_name last_name
Susan Brown Peggy Brown

This is correct but it's a bit confusing as we have a first_name and last_name column in both tables, so let's add some column aliases to make the output a bit clearer. We will also add TABLE ALIASES so we don't have to keep typing in the full name of the table

select p.first_name 'Parent First Name' ,
p.last_name 'Parent Last Name',
c.first_name 'Child First Name' ,
c.last_name 'Child Last Name'
from QiSQL_Parents p ,
QiSQL_Children c
where p.id = c.parent_id;

Ooh! We have added not just aliases to the column names but aliases to the table names! Here's what your output should look like now:

Parent First Name Parent Last Name Child First Name Child Last Name
Susan Brown Peggy Brown

Ok so that's enough for now. Later we will discuss how we can show all the people including the ones that have no children through the magic of outer joins.