MySQL – How to Extract Names from Another Table

MySQLselect

I have data in two tables. One has order details, the other has product details. The order_details table references the product table via the product_id column. The product table has columns product_id and product_name.

I count sales per product ID like this:

SELECT product_id, COUNT(*)
FROM order_details
GROUP BY product_id
ORDER BY COUNT(*) DESC

and get output like this:

product_id  
123         2500
421         1700
111         1040

Now I want to have the names from the product table instead of the IDs: 

apples  2500
banana  1700
pear    1040

How can I do that?

Best Answer

The (possibly) most efficient way, first group, then join:

SELECT
    product_name, c
FROM
    (
      SELECT product_id, COUNT(*) AS c
      FROM order_details
      GROUP BY product_id
    ) AS counts
    /* The trick is now to JOIN this virtual table with your product table */
    JOIN product p ON p.product_id = counts.product_id
ORDER BY 
    c DESC ;

You can check that it works in this SQL Fiddle (scaled by a 10 factor).

This SQL is very close to standard, so it should work not only with MySQL, but with most SQL databases (I've tested it with MySQL, PostgreSQL and SQLite).

Most contemporary databases will probably execute the following query with the same efficiency, provided that NOT NULL and REFERENCES are in place (and you type a bit less, and probably make it easier to understand):

SELECT 
    p.product_name, COUNT(*) AS c
FROM 
    order_details od
    JOIN product p ON p.product_id = od.product_id
GROUP BY 
    p.product_id
ORDER BY 
    count(*) DESC ;

Or, without using aliases:

SELECT 
    product.product_name, COUNT(*) AS c
FROM 
    order_details
    JOIN product ON product.product_id = order_details.product_id
GROUP BY 
    product.product_id
ORDER BY 
    count(*) DESC ;
Related Question