JOIN

The advantage of using a relational database management system is that the data can be easily joined together from different tables and can be returned to the user in a single bulk. This greatly improves the productivity of the developers as it allows fluidity and flexibility when it comes to designing a complex database structure.

There are many types of JOIN statements in MariaDB/MySQL—INNER JOIN, FULL OUTER JOIN, LEFT JOIN, and RIGHT JOIN. All of these different JOIN statements behave differently when executed, which you can see in the following image:

Most of the time, we'll be using the INNER JOIN statement, as it only returns the data that has matching values in both tables, and thus only returns a small amount of the data that is needed. The JOIN command is much more complicated than the others as you need to design the tables to be join-able in the first place. Before we start testing the JOIN command, let's create another table to make this possible. We will call this new table department:

After that, add two departments, like so:

Then, go to the user table, and at the structure page, scroll all the way to the bottom and look for the form shown, then click the Go button:

Add a new column called deptID (which stands for department ID) and set its data type to int (integer number):

After that, set up a few test users and put each of their deptID as either 1 or 2:

Please notice that I have also added the status column here for checking whether the user has been deleted or not. Once you have done with that, let's try to run a sample command!:

SELECT my_user.username, department.name FROM (SELECT * FROM user WHERE deptID = 1) AS my_user INNER JOIN department ON department.id = my_user.deptID AND my_user.status = 0 

That looks quite complicated at first glance, but it really isn't if you separate it into a few parts. We'll start from the command within the () bracket first, in which we asked MariaDB/MySQL to select all columns within the user table that carry deptID =  1:

SELECT * FROM user WHERE deptID = 1 

After that, contain it within a () bracket and name this entire command as my_user. After that, you can start joining your user table (now called my_user) with the department table by using the INNER JOIN statement. Here, we also added some conditions for it to look up the data, such as the ID of the department table must match the deptID of my_user, and the status value of my_user must be 0, indicating that the data is still valid and not tagged as removed:

(SELECT * FROM user WHERE deptID = 1) AS my_user INNER JOIN department ON department.id = my_user.deptID AND my_user.status = 0 

Lastly, add the following code in front to complete the SQL command:

SELECT my_user.username, department.name FROM  

Let's try the preceding command and see if the result is what you expected.

You can join infinite numbers of tables using this method as long as the tables are linked to each another through matching columns.

There are many other SQL statements that we have not covered in this chapter, but the ones that we have covered are pretty much all you need to get started.

One last thing before we move on to the next section—we must create a user account for the application to access to our MariaDB/MySQL database. First of all, go to your phpMyAdmin home page and click User accounts on the top menu:

Then, go to the bottom and look for this link called Add user account:

Once you're in the Add user account page, type in the User name and Password information in the Login Information form. Make sure that the Host name is set to Local:

Then, scroll down and set the Global privileges of the user. Enabling the options within the Data section is well enough, but do not enable the other options as it might give hackers the privilege to alter your database structure once your server has been compromised:

Once you have created the user account, follow the following steps to allow the newly-created user access to the database called test (or any other table name of your choice):

After you have clicked the Go button, you have now given the user account the privilege to access the database! In the next section, we'll be learning how to connect our Qt application to the database.