LukeJ LukeJ - 3 years ago 196
MySQL Question

SQL Inner Join 2 Tables

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.

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download