O'Reilly logo

LPI Linux Certification in a Nutshell, 3rd Edition by James Stanger, Bruno Gomes Pessanha, Stephen Addison Schneiter, Adam Haeder

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Multitable Queries

The final concept to describe in our basic SQL overview is that of JOIN. So far, we have concerned ourselves with querying only one table. This is fine for simple data storage requirements, but as data complexity grows, so does the need for multiple tables. As long as there is a relationship between data elements in the tables, the table values can be JOINed in a query.

To see an example of this, we need to create another table that has a relationship with the first table. We will use this second table to store pet information for each family.

mysql> CREATE TABLE pets (
    ->   id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   family_id INTEGER UNSIGNED NOT NULL,
    ->   type VARCHAR(45) NOT NULL,
    ->   name VARCHAR(45) NOT NULL,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------------+
| Tables_in_community |
+---------------------+
| families            |
| pets                |
+---------------------+
2 rows in set (0.00 sec)

mysql> describe pets;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| family_id | int(10) unsigned | NO   |     |         |                |
| type      | varchar(45)      | NO   |     |         |                |
| name      | varchar(45)      | NO   |     |         |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

mysql> insert into pets (family_id,type,name) VALUES ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required