Manage JOIN three tables

In the guide to SQL we have seen how to create partnerships between two tables using INNER JOIN. In this article we will see how to do the same thing as managing no more than two, but three tables.

In reality it is a very simple operation which does not differ from a common JOIN of 2 tables.
Let's make an example: think of a hypothetical purchase of a simple database of e-commerce consists of three tables structured as follows:

users (used for the management of registered users)

• ute_id
• ute_nome
• ute_cognome

products (used for the catalog of products for sale)

• prod_id
• prod_nome
• prod_descrizione
• prod_prezzo

purchases (used to record purchases)

• acq_id
• acq_ute_id
• acq_prod_id
• acq_data

Now let's say you want to retrieve, using a single query, the following information related to purchases made ​​by a given user ID (eg. 1), information contained in three separate tables: full name of the user (users table), name, description and the product purchase price (the Products table) and the date of purchase (table purchases). How? Simple ... just use a query like this:

 SELECT



  



 utenti.ute_nome, utenti.ute_cognome,



  



 prodotti.prod_nome, prodotti.prod_descrizione, prodotti.prod_prezzo,



  



 acquisti.acq_data







 FROM users







 INNER JOIN purchases







 ON = utenti.ute_id acquisti.acq_ute_id







 INNER JOIN products







 ON = prodotti.prod_id acquisti.acq_prod_id







 WHERE utenti.ute_id = 1;

 

As you may have noticed the common FROM clause (which identifies the first table) is followed by two INNER JOIN (which identify the second and the third table) accompanied by the applicable link ON that is "a bridge" in order to create a link rationally between the different tables.

Finally I just have to remind you that, of course, you can use the technique seen above for more complex queries involving 4, 5 or more tables ... in fact I advise you not to exaggerate the construction of such queries, although theoretically feasible, is often labored manageable in practical application.