Understanding subqueries!

[shivajikobardan]

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.

 

[easypeasy]

I don't know of any resources but I can try to explain why your attempt didn't work. Your query assumes that orderid is in the customer's table. 

 

Here is an online book that may help

https://runestone.academy/ns/books/published/practical_db/PART1_SQL/08-subqueries/subqueries.html

[Franck]

Sub queries are usually bad. You try to avoid as much as possible because they can create massive ram usage.

With limited proper uses of join you can alleviate most of these problems.

 

So you would rather try to have a query that look much more like the following :

 

SELECT TOP 1 customers.ContactName 
FROM orderdetails WITH(NOLOCK)
LEFT JOIN orders WITH(NOLOCK) ON orderdetails.OrderID = orders.OrderID
LEFT JOIN customers WITH(NOLOCK) ON orders.CustomerID = customers.CustomerID
ORDER BY (orderdetails.unitprice * orderdetails.quantity) DESC

 

[wanderingfool2]

@shivajikobardan

 

The error you posted has nothing really to do with subqueries really.

 

orderid simply does not exist in the table customers.

 

You'd get the same error if you did

SELECT 
    contactname
FROM
    customers
WHERE
    orderid IN [1,2,3,4]

 

In cases like this ask yourself, where is the variable coming from? 

[shivajikobardan]

thank you all for your replies.