SQL Hasmany Query select 1

I have two tables which I am trying to query.

Stock_Table

• Stock_Code
• Stock_Name
• Physical_Stock

Order_Table

• Order_Date
• Quantity
• Stock_Code

I’m after all stock records where the physical stock is greater than 0 and the last date it was ordered.

I thought the following query would do it but this duplicates the stock records per how many orders there are.

SELECT
    Stock_Code,
    Physical_Stock,
    Order_Date
FROM Stock_Table
INNER JOIN Order_Table
    ON Stock_Table.Stock_code = Order_Table.Stock_Code
WHERE Physical_Stock <> 0

Answer

Greater than 0 or <> 0 which is it?

You just need to use aggregate function max() to get the max order date since multiple orders could be placed for a given stock code. and since you use aggregation, a group by is needed for the non-aggregated columns.

SELECT Stock_Code, Physical_Stock, max(Order_Date)
FROM Stock_Table 
INNER JOIN Order_Table 
  ON Stock_Table.Stock_code = Order_Table.Stock_Code
WHERE Physical_Stock <> 0
GROUP BY Stock_Code, Physical_Stock