mysqlsqldatabase

SQL query: Get the most expensive product ordered in each month of 2011

I don't understand how to "combine" two tables so that I can generate an answer to this.

Basically, I have a CAP database and I'm only concerned with two tables:

ORDERS (ordno, month, year, cid, aid, pid, qty, dollars)
PRODUCTS (pid, pname, city, quantity, price)

I know that I need to generate a new table like the following to get the answer:

Temp (month, year, pname, price)

The way I tried to do it is:

Temp = Select * from (

(Select month, year from Orders)

join

(Select price, pname from Products)

on

orders.pid = products.pid )

The above query gives me an error that I don't understand: Every derived table must have its own alias.

The next step I'm pretty sure is:

Answer = select max(price), pname, month from Temp where year = 2011 group by month;

I am very new to SQL. If you guys could help me that would be great. If there is a website that can help me learn SQL better please let me know.

EDIT: Also if there is more than one product that has max price then you need to list all of them for that month.

Solution

  • EDIT

    If the price of the product ever changes, you will not get a meaningful result.

    SELECT P.pid, P.price, O.month, P.pname FROM 
products as P
JOIN orders AS O ON
    P.pid = O.pid       
JOIN (
    SELECT
        MAX(P.price) AS price
        , O.month           
    FROM
        orders AS O
        LEFT JOIN products AS P ON
            O.pid = P.pid
    WHERE
        O.year = 2011
    GROUP BY
        O.month
) AS MX ON
    MX.month = O.month
    AND O.year = 2011
    AND P.price = MX.price

    FYI This is how you give a derived table an alias.

    (Select month, year from Orders) AS myAlias