Hoping to get some help with this, I have made a few attempts at an inner join that shows all 'Product' information from the product table for, any product that has sold more than 10 units using an inner join.
PRODUCT TABLE (Columns)
P_CODE, P_DESCRIPT, P_INDATE, P_QOH, P_MIN, P_PRICE, P_DISCOUNT, V_CODE
LINE TABLE (Columns) this table shows the lines/information for each
invoice
INV_NUMBER, LINE NUMBER, P_CODE, LINE_UNITS, LINE_PRICE, LINE_TOTAL
I understand that I have to make the join using the common key attribute (p_code) but I cannot figure out how to do the sum within the inner join.
Here is my most recent attempt:
SELECT * PRODUCT FROM PRODUCT
INNER JOIN line
ON product.p_code = line.p_code
WHERE sum(line_units) >=10
AND line.p_code = product.p_code;
Error: near "product"; syntax error
Any help would be appreciated, Thank you.
Looks like you have the table name PRODUCT within the SELECT section. And the sum() needs to happen within the SELECT section along with the extra HAVING clause at the end.
SELECT *, sum(line_units) as line_units_sum FROM product
INNER JOIN line ON product.p_code = line.p_code
WHERE line.p_code = product.p_code
HAVING line_units_sum >= 10