How to select everything from one table and a row from another table in SQL

Question

I want to select everything from the table PRODUCT_INFORMATION. I tried to use p.* and PRODUCT_INFORMATION.* but it's not working? Where is my mistake? Is it something with JOIN?

SELECT  
p.PRODUCT_NAME , 
SUM (oi.QUANTITY) TOTAL_QUANTITY, 
o.ORDER_MODE

FROM PRODUCT_INFORMATION p
    LEFT JOIN ORDER_ITEMS oi ON p.PRODUCT_ID = oi.PRODUCT_ID
    LEFT JOIN ORDERS o ON o.ORDER_ID = oi.ORDER_ID
        WHERE INSTR (p.PRODUCT_NAME, '<') = 0
        AND INSTR (p.PRODUCT_NAME, '_') = 0
    GROUP BY p.PRODUCT_NAME, p.WARRANTY_PERIOD, o.ORDER_MODE
    HAVING SUM (oi.QUANTITY) > 200
    ORDER BY p.WARRANTY_PERIOD;

Answer 1

In MySQL, you can aggregate by the primary key of a table and still accept all the columns. Assuming that PRODUCT_ID is the primary key, you can use:

SELECT p.*, SUM(oi.QUANTITY) as TOTAL_QUANTITY, o.ORDER_MODE
FROM PRODUCT_INFORMATION p LEFT JOIN
     ORDER_ITEMS oi
     ON p.PRODUCT_ID = oi.PRODUCT_ID LEFT JOIN
     ORDERS o
     ON o.ORDER_ID = oi.ORDER_ID
WHERE INSTR (p.PRODUCT_NAME, '<') = 0 AND
      INSTR (p.PRODUCT_NAME, '_') = 0
GROUP BY p.PRODUCT_ID, o.ORDER_MODE
HAVING SUM (oi.QUANTITY) > 200
ORDER BY p.WARRANTY_PERIOD;