Howto – A short MySQL tutorial with examples – 03
In our previous MySQL tutorial, we did some basic SELECT queries, a CONCAT for output and a JOIN to lookup data on a different table that was linked or RELATED to our client database. In this MySQL tutorial, we will continue with a DOUBLE JOIN and discuss UPDATES and DATA MANIPULATION. We will also have a look at some different JOIN types.
Our data from our previous MySQL tutorials
The DOUBLE JOIN
Let us now construct a DOUBLE JOIN to find the income bracket of a client. We need to use a DOUBLE JOIN as there is no DIRECT RELATION between income bracket and client. Thus we have to SELECT the clients, find their income category from the clientdetails table. We then use this to find the income bracket from the catdef table. Visually, it can be represented as follows:
We will alias each table to enable a shorter, more readable query. The clients table will be C, the clientdetails will be D and the catdef table will be CD. We thus SELECT the data to be shown and do a DOUBLE JOIN to match the correct data. Here is the query and results:
SELECT C.fname,C.lname,CD.bracket FROM clients C JOIN clientdetails D ON C.idx=D.idxp JOIN catdef CD ON D.incomecat=CD.incomecat ; +-------+--------+---------+ | fname | lname | bracket | +-------+--------+---------+ | Susan | Harris | 20000 | | Peter | Jones | 20000 | | Joe | Smith | 30000 | +-------+--------+---------+ 3 rows in set (0.01 sec)
Updating a table and inserting new data
We are actually going to be answering three question by explaining two concepts. How would we add a SECOND vehicle to an existing client and how do we UPDATE existing data. Let us first ADD a new vehicle entry for our client, Joe Smith (client #1).
INSERT INTO clientvehicles VALUES ('1','Porsche','Boxster S','2017','50','JOESMITH-CA','2018-04-26','','0','Hanbrake Adjusted','New Vehicle');
Query OK, 1 row affected, 1 warning (0.02 sec)
SELECT * FROM clientvehicles;
+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
| idxv | make | model | year | mileage | reg | servicedate | lastservice | lastmileage | workdone | notes |
+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
| 1 | Jaguar | XF | 2015 | 12598 | CA 223344 | 2018-04-11 | 2017-01-15 | 6521 | Oil & Filters | None |
| 2 | Ford | Mondeo | 2013 | 52344 | CF 123212 | 2017-11-23 | 2015-12-03 | 34565 | Oil, Filters & Plugs | Left rear tyre bald patch |
| 3 | Mini | Cooper S | 2010 | 87433 | CY 982212 | 2018-01-15 | 2017-01-12 | 70211 | Oil, Filters & Plugs | LF CV Joint Boot worn |
| 1 | Porsche | Boxster S | 2017 | 50 | JOESMITH-CA | 2018-04-26 | 0000-00-00 | 0 | Hanbrake Adjusted | New Vehicle |
+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+
4 rows in set (0.00 sec)
Now we have two vehicles with the SAME client id (idxv). Let us SELECT all vehicles for Joe Smith. At the same time, we use CONCAT to clean up the output.
SELECT CONCAT(V.make," ",V.model) AS car, CONCAT(C.fname," ",C.lname) AS owner FROM clientvehicles V JOIN clients C ON V.idxv=C.idx WHERE C.fname LIKE 'joe' AND C.lname LIKE 'smith'; +-------------------+-----------+ | car | owner | +-------------------+-----------+ | Jaguar XF | Joe Smith | | Porsche Boxster S | Joe Smith | +-------------------+-----------+ 2 rows in set (0.00 sec)
Next, we will let MySQL do some calculations for us. How many cars does Joe Smith have in our system? We use the COUNT() command to count item occurrences.
SELECT COUNT(V.idxv) AS number, CONCAT(C.fname," ",C.lname) AS owner FROM clientvehicles V JOIN clients C ON V.idxv=C.idx WHERE C.fname LIKE 'joe' AND C.lname LIKE 'smith'; +--------+-----------+ | number | owner | +--------+-----------+ | 2 | Joe Smith | +--------+-----------+ 1 row in set (0.00 sec)
Let us update the information on Joe Smith’s Jaguar – he has a new registration which is now “JOESJAG’. We are going to add a UNIQUE id to the start of the table as we cannot use the idxv field to uniquely id the row anymore.
ALTER TABLE clientvehicles ADD COLUMN idx INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 SELECT * FROM clientvehicles; +-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+ | idx | idxv | make | model | year | mileage | reg | servicedate | lastservice | lastmileage | workdone | notes | +-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+ | 1 | 1 | Jaguar | XF | 2015 | 12598 | CA 223344 | 2018-04-11 | 2017-01-15 | 6521 | Oil & Filters | None | | 2 | 2 | Ford | Mondeo | 2013 | 52344 | CF 123212 | 2017-11-23 | 2015-12-03 | 34565 | Oil, Filters & Plugs | Left rear tyre bald patch | | 3 | 3 | Mini | Cooper S | 2010 | 87433 | CY 982212 | 2018-01-15 | 2017-01-12 | 70211 | Oil, Filters & Plugs | LF CV Joint Boot worn | | 4 | 1 | Porsche | Boxster S | 2017 | 50 | JOESMITH-CA | 2018-04-26 | 0000-00-00 | 0 | Hanbrake Adjusted | New Vehicle | +-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+ 4 rows in set (0.00 sec)
Now that we have a UNIQUE reference, we can do the UPDATE. Here we SET the field reg to ‘JOESJAG’ WHERE the idx field equals 1.
UPDATE clientvehicles SET reg='JOESJAG' WHERE idx=1; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM clientvehicles; +-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+ | idx | idxv | make | model | year | mileage | reg | servicedate | lastservice | lastmileage | workdone | notes | +-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+ | 1 | 1 | Jaguar | XF | 2015 | 12598 | JOESJAG | 2018-04-11 | 2017-01-15 | 6521 | Oil & Filters | None | | 2 | 2 | Ford | Mondeo | 2013 | 52344 | CF 123212 | 2017-11-23 | 2015-12-03 | 34565 | Oil, Filters & Plugs | Left rear tyre bald patch | | 3 | 3 | Mini | Cooper S | 2010 | 87433 | CY 982212 | 2018-01-15 | 2017-01-12 | 70211 | Oil, Filters & Plugs | LF CV Joint Boot worn | | 4 | 1 | Porsche | Boxster S | 2017 | 50 | JOESMITH-CA | 2018-04-26 | 0000-00-00 | 0 | Hanbrake Adjusted | New Vehicle | +-----+------+---------+-----------+------+---------+-------------+-------------+-------------+-------------+----------------------+---------------------------+ 4 rows in set (0.00 sec)
JOIN TYPES explained and demonstrated
This is best shown with a diagram from WikiMedia
Happy Hosting!
