Cómo consultar y relacionar tablas en MySQL

Are you starting with databases and want to know how to query useful information from various tables in MySQL? Learning how to relate tables correctly allows you to get valuable information and make better decisions based on real data.

Which tables are important in our project?

When working with relational databases in MySQL, tables are fundamental. In this example there are four key tables:

• bills: the main table that records accounts and payment status.
• clients: identifies who makes the purchase, related through the ClientID field.
• bill products: connects each invoice to specific products purchased.
• products: contains details such as name and current price of the products.

Each table has specific relationships that make it easy to extract specific information.

How to build effective queries using joins?

Building SQL queries using joins allows you to gather relevant data from multiple tables. Here's how to do it step by step:

How to get a list of invoices with essential data?

You start a basic query with the main table:

SELECT  bill.billID,  bill.status,  clients.nameFROM bills AS billLEFT JOIN clients AS clients ON bill.clientID  = clients.clientID;

This type of query returns essential data: account number, status and name of the linked customer.

How to count products sold per invoice?

The query can be extended to count how many products were purchased on each invoice:

SELECT  bill.billID,  bill.status,  clients.name,  COUNT(bp.billproductID) AS number_of_productsFROM bills AS billLEFT JOIN clients AS clients ON bill.clientID  = clients.clientIDLEFT JOIN billproducts AS  bp ON  bp.billID  = bill.billIDGROUP BY bill.billID;

This modification uses count() to know exactly how many products each bill has.

How to calculate the final amount of an invoice including discounts?

To have a deeper detail and consider discounts, another join is added to the products table to get current prices:

SELECT  bill.billID,  bill.status,  clients.name,  COUNT(bp.billproductID) AS number_of_products,   ROUND(SUM(bp.quantity  * product.price  * (1  -  bp.discount  / 100)),  2) AS totalFROM bills AS billLEFT JOIN customers AS customers ON bill.clientID  = clients.clientIDLEFT JOIN billproducts AS  bp ON  bp.billID  = bill.billIDLEFT JOIN products AS product ON  bp.productID  = product.productIDGROUP BY bill.billID;

With this query, you automatically get a detailed summary of purchases with the total amount including discounts applied.

Why is it useful to concatenate results in SQL queries?

If you want your report to be easily readable, concatenating values into text is an excellent option:

SELECT  CONCAT(clients.name, ' has an account ', bill.status, ' with ', COUNT(bp.billproductID), ' products and sum ',  ROUND(SUM(bp.quantity  * product.price  * (1  -  bp.discount  / 100))),  2)) AS resultFROM bills AS billLEFT JOIN clients AS clients ON bill.clientID  = clients.clientIDLEFT JOIN billproducts AS  bp ON  bp.billID = bill.billID LEFT JOIN   products AS product ON  bp.productID  = product.productIDGROUP BY bill.billID;

This generates clear sentences that easily summarize business information.

What is the real value of knowing how to use databases?

Understanding databases and the right way to leverage their potential greatly helps developers and data scientists to derive real value, make informed decisions and define sound, evidence-based strategies. A carefully structured database not only facilitates quick access to accurate information, it also provides flexibility and sustainable growth in the future.

Did you find this guide to SQL queries useful? Share your ideas and experiences about databases in the comments!