Skip to content
Advertisement

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

Advertisement

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
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement