Understanding subqueries!

[polaryeti]

We want to know the best customer of Northwind database by the highest total amount spent.

https://brucebauer.info/assets/ITEC3610/Northwind/Northwind-Sample-Database-Diagram.pdf

This is the northwind database diagram.

My try

SELECT 
    contactname
FROM
    customers
WHERE
    orderid IN (SELECT 
            orderid
        FROM
            orderdetails
        HAVING MAX(unitprice * quantity));

        
This is producing the below error:

Error Code: 1054. Unknown column 'orderid' in 'IN/ALL/ANY subquery'

This is the answer provided.
 

SELECT 
    *
FROM
    customers
WHERE
    customerid = (SELECT 
            customerid
        FROM
            orders
        WHERE
            orderid = (SELECT 
                    orderid
                FROM
                    orderdetails
                GROUP BY orderid
                ORDER BY SUM(unitprice * quantity) DESC
                LIMIT 1));

I am trying to understand sub queries since five hours, but nothing is getting into my head. Can you please guide me towards some good tutorials/books/courses that'd help me understand subqueries. I prefer books at this moment.

[gizmola]

Simple subquery article:  https://www.guru99.com/sub-queries.html

A subquery is exactly what the name describes:  An inner (sub) query that is run, with a result that is then used by an outer query.  It is not complicated.

In order for it to be used in a "WHERE column =" the subquery must return at most 1 value.  If it can return multiple rows/values, then you need to use  "where column IN" or possibly NOT IN.

My 1st tip:  a subquery can't possibly work as a subquery, if it doesn't run by itself in standalone fashion.  You want to investigate, whether or not you can use a HAVING clause without a GROUP BY.

[polaryeti]

thank you for your answer.