Displaying Company Name Instead of Supplier ID in MySQL

In this article, we will explore how to display company names instead of supplier IDs in a MySQL database. We will start with an example of how to calculate total sales for each supplier, and then modify the query to include the supplier name.

The Problem

We have three tables: purchase, product, and suppliers. The purchase table contains information about purchases made by customers, including the product purchased (list) and the quantity ordered. The product table contains information about the products offered for sale, including their prices. The suppliers table contains information about the suppliers who provide these products.

The current query calculates the total sales for each supplier:

mysql> SELECT purchase.sup_id, SUM(purchase.quantity*product.price) 
           FROM product JOIN purchase ON product.name = purchase.list 
           GROUP BY purchase.sup_id;
+--------+--------------------------------------+
| sup_id | SUM(purchase.quantity*product.price) |
+--------+--------------------------------------+
|      1 |                                29000 |
|      2 |                                19250 |
|      3 |                                 5100 |
|      4 |                                 3050 |
+--------+--------------------------------------+

This query works well, but it would be more user-friendly to display the supplier name instead of the supplier ID.

The Solution

The solution involves adding another join to bring in the suppliers table and displaying the supplier name in the select clause.

select su.name, sum(pu.quantity * pr.price) total_sales
from product pr
inner join purchase pu on pr.name = pu.list 
inner join suppliers su on su.sup_id = pu.sup_id
group by pu.sup_id, su.name;

This query works as follows:

We start with the product table and perform an inner join with the purchase table on the name column (i.e., the product name). This allows us to link each purchase to its corresponding product.
We then perform another inner join with the suppliers table on the sup_id column, which links each purchase to its supplier.
The resulting query includes both the product and supplier information in the select clause.
Finally, we group the results by both the supplier ID (pu.sup_id) and name (su.name). This allows us to calculate the total sales for each supplier.

Using Table Aliases

To make the query even shorter and easier to read, we can use table aliases. These are shortcuts that allow us to refer to tables using shorter names instead of their full names.

For example:

select su.name, sum(pu.quantity * pr.price) total_sales
from product pr
inner join purchase pu on pr.name = pu.list 
inner join suppliers su on su.sup_id = pu.sup_id
group by pu.sup_id, su.name;

In this query, pr, pu, and su are table aliases for the product, purchase, and suppliers tables, respectively. Using these aliases makes the query shorter and easier to read.

Conclusion

To display company names instead of supplier IDs in MySQL, you can use a simple modification to your existing query. By adding another join to bring in the suppliers table and displaying the supplier name in the select clause, you can achieve this goal.

We hope that this article has provided you with a better understanding of how to work with tables and joins in MySQL.

Last modified on 2025-03-15