How to join the same table three times in a single SQL query statement?

Having more than one join to the same table can be confusing.

More than one join can quickly create a messy SQL statement. If you don’t use aliases they can quickly become difficult to read.

If your database tables need to have three or more columns with the same relationship to another table. You can easily join them with the help of aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored. Finally a Users id is recorded to approve the changes.

Products table fields: Comments
id Primary Key
created_by_user_id Foreign Key to Users table
modified_by_user_id Foreign Key to Users table
approved_by_user_id Foreign Key to Users table
Users table fields: Comments
id Primary Key
first_name
last_name

If you want to find out who created the product, you can do that with a simple SQL query like this:

SELECT
p.id
, u.firstname AS 'creator_firstname'
, u.lastname AS 'creator_lastname'
FROM products p
INNER JOIN users u
ON p.created_by_user_id = u.id

If you want to find out who modified the product you can do that with a similar query:

SELECT
p.id
, u.firstname AS 'modifier_firstname'
, u.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u
ON p.modified_by_user_id = u.id

If you want to find out who approved the product you can do that with a similar query:

SELECT
p.id
, u.firstname AS 'approver_firstname'
, u.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u
ON p.approved_by_user_id = u.id

But how can you combine all three queries to know the usera that created, modified and approved the products in one query?

Just join the Users table three times, but you need to use a different alias each time you JOIN the same table:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
, u3.firstname AS 'approver_firstname'
, u3.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id
INNER JOIN users u3
ON p.approved_by_user_id = u3.id

But what if a product has only been created and modified but hasn’t yet been approved? In which case the approved_by_user_id should be null in your products table.  Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t.  In this case you need to use a LEFT JOIN like this:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
, u3.firstname AS 'approver_firstname'
, u3.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id
LEFT JOIN users u3
ON p.approved_by_user_id = u3.id

So now you can join the same table three times with aliases in single efficient query.

Here are all the SQL statements from above and the CREATE table statements:


For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please pop your email in the box below and you’ll be sure to receive them!

Pros and cons for switching to MariaDB from MySQL?

What are the advantages of switching to MariaDB from MySQL?

And are there any disadvantages?

If you like me, you may have only just heard about MariaDB. I came across it as a new option when adding a database to RackSpace Cloud hosting environment. After a quick read up about MariaDB and seeing that it was MySQL compatible. I thought why not give it a shot for the website I was working on. The website was a demo site, so there weren’t any concerns about production stability. Rackspace Cloud Hosting provided PHPMyAdmin for accessing the MariaDB. Just like you would have with MySQL. Everything appeared to look the same and work the same setting up the database. Once set-up I immediately saw that MariaDB is definitely faster than MySQL.

So what are the pros and cons for switching to MariaDB from MySQL?

Pros of MariaDB vs MySQL

  1. MariaDB has better query performance.
  2. MariaDB has more of an open source attitude.
  3. Switching to MariaDB is easy.
  4. Galera implementation is better in MariaDB.
  5. MariaDB is available as an option with some hosting environments, like RackSpace Cloud.
  6. MariaDB comes by default with some distros, like the Red Hat series.

I took a big site from MySQL to MariaDB with zero issues.

Cons of MariaDB vs MySQL

  1. Switching back from MariaDB to MySQL may not be so easy.
  2. If you are using AWS, Amazon Aurora may be even faster than MariaDB and MySQL.
  3. Your Operating System may not support MariaDB yet.
  4. Your hosting environment may not support MariaDB yet.
  5. Percona may be an even better option than MariaDB.

As with all software there are new versions in development. Each release bringing better performance with it.

So if you are looking to switch from MySQL, make sure the benefits outweigh the risks.

Wikipedia made the switch in 2013 as outlined in this blog post

Rackspace is updating all it’s Cloud Sites from MySQL to MariaDB in 2016.

Useful knowledge base articles from Maria:

If you liked this post, be sure to subscribe below and be the first to get my next post…

When do you need to use back ticks with MySQL?

Back ticks are often misunderstood.

I am trying to learn the best way to write queries.

You might not be sure how to use those funny little `ticks

`back ticks`, `backticks`

I have used single quotes, double quotes and back ticks without any real thought.

What is the standard for this?

Back ticks should be used for table and column identifiers, but are only needed when the identifier is MySQL reserved keyword.
Also if the identifier contains white space or other special characters.

When possible it’s recommended not to use keywords and special characters as table and column identifiers to avoid having to use back ticks.

If you use PHPMyAdmin to generate some queries you will see that all the column and table identifiers are escaped with back ticks by default.

For example a SELECT query would look like this:

SELECT `id`, `created_by_user_id`, `modified_by_user_id` FROM `products` WHERE 1

As none of these column and table identifiers contain any special characters and don’t match any of the MySQL reserved keywords, the same query will run just fine without the back ticks:

SELECT id, created_by_user_id, modified_by_user_id FROM products WHERE 1

Replace the back ticks with single quotes (or double quotes) and you will get an error as now each identifier wrapped in single quotes is treated as a string:

--DON'T DO THIS, AS IT WILL RAISE THE ERROR BELOW:
SELECT 'id', 'created_by_user_id', 'modified_by_user_id' FROM 'products' WHERE 1
--#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''products'

If you liked this tip, be sure to subscribe below and be the first to get my next tip…

How to use CodeIgniter to prevent malicious SQL injections

Should you escape input when using CodeIgniters active record class?

Do you need to worry about MySQL injection vulnerabilities when using CodeIgniter?

Yes, yes you do.

It is of course good security practice. To escape your data before submitting it into your database.

CodeIgniter provides three helpful methods. Which are part of it’s database library to stop SQL code injection.

$this->db->escape()
$this->db->escape_str()
$this->db->escape_like_str()

CodeIgniter also provides a method to to use Bindings. Whereby the question marks are replaced by the data parameters. That you pass to the query function.

For example:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));

This way they are escaped so you don’t have to escape them. Resulting in safer queries for you. Stopping attackers from injecting malicious SQL.

Refer to the last two sections of this page of the CodeIgniter manual.

If you liked this tip, be sure to subscribe below and be the first to get my next tip…

MySQL terminology guide for newbies

MySQL Terminology helpful guide

If you are newbie to MySQL, this guide explains some terminology that is used when developing with MySQL and PHP.

This is not meant to be a definitive guide, but will be updated. Did I miss something? Please let me know or leave a comment below.

Aliases for column names Produces a more readable column name if needed. Especially when a column is produced from a function like CONCAT() for example
Aliases for table names Makes queries shorter and easier to read / write
Backticks ` MySql’s (non-standard) method for delimiting problematic identifiers
Composite Index An index made of more than one column.
Database A collection of one or more tables. Think of a Excel workbook containing many sheets.
MySQL client Software to allow to connect and manage MySQL databases. MySQL Workbench, HeidiSQL, phpMyAdmin etc…
ORDER BY clause Used to sort the results of your SELECT query by one or more columns in ascending or descending order.
Primary Key A unique identifier of a row consisting of one or more columns
Query A way of SELECTing / INSERTing / DELETEing / UPDATEing data
Results / Result set The data / result of a query being run
Row / Record A single row or record of data in a table
Schema The design of one or more tables
SQL Structure Query Language
Sub query A quested nested within another query
Table A repository of data (entity). Think of a sheet in Excel with column headers.

If you liked this guide, be sure to subscribe below and be the first to get my next post…

How to use a local fallback in the event a CDN isn’t available for jQuery

Worried about using a CDN?

  • Worried about a CDN being down?
  • Concerned a CDN might slow your site down?
  • Thinking there must be a down side to accessing files from a CDN?

Fear not a CDN (Content Delivery Network) is a globally distributed network of servers deployed in many data centers. Giving you several advantages:

  • A CDN is unlikely to be down, after all, up time is one of the big selling points of CDN.
  • Decreased latency so that a user accessing your site will be able to download the content faster
  • Increased parallel connections can be made. Again increasing the speed your content and site can download to a users browser
  • Cross-site caching if other sites your users have visited also use the same CDN. jQuery may already be cached in their browser

If that hasn’t allayed your fears? Then you can always use a fallback for a local version of jQuery like this:

If you found this post useful, please subscribe below. If you have any questions, don’t hesitate to leave a comment…

How to integrate a WordPress.com blog RSS feed into a PHP page

What comes first your wordpress blog or your website?

You might have started with a free wordpress blog. Then later created a separate website or vice versa. Well whichever way round it is. You might have ended up with two disconnected websites. Now you want to reuse your wordpress.com blog content in your website.

One easy way to do this and integrate your blog content in your website is using its RSS feed. Which is available on your wordpress blog. Just add this query string “?feed=rss” to your blog’s url and you will get an RSS feed from your blog.

Try it now in your web browser, by clicking on this link:

https://element121.com/?feed=rss

Now that you can access the RSS feed, you can parse the RSS data into your page

Last RSS is a PHP RSS parser. Available to download as a single file here: http://lastrss.oslab.net/

So you can now parse the RSS feed. With a few lines of PHP code as demonstrated below. This is based on one of Last RSS’s examples.

If you found this post useful, please subscribe below. If you have any questions, don’t hesitate to leave a comment…

How to replace NULL values in a MySQL SELECT statement

Sometimes when you are SELECTing data from MySQL, some of your columns may have NULL data values and NULL’s don’t display well to end-users, so it may be best to replace the NULL’s with a string of text instead such as:

  • “Not defined”
  • “Undefined”
  • “Default value”

You could do this in PHP when you are echoing out your data, but it’s a lot easier to do it your MySQL statement.

For example if you have this simple SELECT statement, but not every manufacturers website is defined, so that the website column value is sometimes null.


SELECT id, manufacturer, website
FROM manufacturers

Then you can simply use the COALESCE function to check for and replace any NULL values with ‘Not defined’.


SELECT id, manufacturer, COALESCE(website, 'Not defined') AS website
FROM manufacturers

Also I’ve given the same column name as an alias to the function so your resulting recordset will have the same column header as your first query.

Did you find the above helpful? Please leave a comment below and let me know…

Why breaking time and tasks in to small blocks will increase your productivity as a web developer

I’m a chronic procrastinator – how do I break it? I could be changing the world, but instead I’m putting in the bare minimum and no matter what trick or method I try I can’t seem to beat it.

Sound familiar?  You know what needs to be done, but put it off…

I always have a fear of finishing something and it’s a bit of a problem for me.  I can’t ever finish anything, because then what??

And if it’s not work to do, there’s always something else you could check… check your email, check the news, check the weather, check your social media updates. Or you could make a tea / coffee, grab a snack or see what’s going on outside….

Endless distractions and procrastination to avoid actually doing the work.  Before you know it half the day has gone and then the whole day and it’s time to stop working.

How do you bring your focus back?

A timer by your desk is a great way to approach this at work, personally I find 15-20 mins of work and 5 mins of procrastinating is a good balance.

If you haven’t heard of it before this is called the Pomodoro Technique!

  1. Pick a task to do
  2. Set a timer to work on the task (25 mins)
  3. When the time is up, stop and take a break doing something else for a set period of time (3-5 mins)
  4. Repeat the above until your task(s) are complete!
  5. After four iterations of the above, take a longer break, you’ve actually earned it!

Much like breaking a complex programming problem up into smaller chunks to fix, breaking time up into defined short periods (Pomodoros) helps you concentrate and stop procrastinating for at least 25 minutes to actually get some work done.

Depending on your work place an actual physical timer on your desk may be a good solution.  Otherwise there are a choice of websites, apps and chrome extensions to time your Pomodoros for you.

Like the above? and interested in receiving more web development focused productivity tips, please subscribe below for a weekly email of any new posts, so you don’t miss out on them.

 

When to use USING in a JOIN rather than JOINing ON?

What is the difference between ON and USING() when JOINing two or more tables together in a SQL query?

For example you have the following two tables, Users and Products, when a Product is created, the Users id that created it is stored.

Products table:
id Primary Key
created_by_user_id Foreign Key to Users table
product_name
Users table:
id Primary Key
first_name
last_name

If you want to find out who created the product, you can do that easily with a simple SQL query like this:

SELECT p.*, u.*
FROM products p
INNER JOIN users u ON p.created_by_user_id = u.id

If however the user id field was named the same in both tables like this:

Products table:
id Primary Key
user_id Foreign Key to Users table
product_name
Users table:
user_id Primary Key
first_name
last_name

You can then use USING to JOIN the tables together and return the same data with this SQL:

SELECT p.*, u.*
FROM products p
INNER JOIN users u USING(user_id)

Each time you JOIN a table you will need to use an additional USING clause even if the second and third tables contain the exactly same column name.

Make sure that when you are JOINing tables together using the ON or the USING clause that the data type of the matching columns is the same data type.

Interested in more SQL tips? Please leave a comment below or pop your email in the box below and you’ll be sure to receive them!