SQL: If there is No match on condition (row in table) return a default value and Rows which match from different tables

Question

I have three tables: Clinic, Stock and StockLog.

I need to get all rows where Stock.stock < 5. I need to also show if an order has been placed and what amount it is; which is found in the table Stocklog.

The issue is that a user can set his stock level in the Stock table without placing an order which would go to Stocklog.

I need a query that can : return the rows in the Stock table and get the related order amounts in the Stocklog table. If no order has been placed in StockLog, then set amount to order amount to zero.

I have tried :

SELECT 
    Clinic.Name, 
    Stock.NameOfMedication, Stock.Stock, 
    StockLog.OrderAmount
FROM 
    Clinic
JOIN 
    Stock ON Stock.ClinicID = Clinic.ClinicID
JOIN  
    StockLog ON StockLog.StockID = Stock.StockID
WHERE 
    Stock.Stock <= 5  

The issue with my query is that I lose rows which are not found in StockLog.

Any help on how to write this.

Thank you.

Answer 1

If you want to keep all the rows that result from joining Clinic and Stock, then use a LEFT OUTER JOIN with StockLog. I don't know which SQL you're using (SQL Server, MySQL, PostgreSQL, Oracle), so I can't give you a precise example, but searching for "left outer join" in the relevant documentation should work.

See this Stack Overflow post for an explanation of the various kinds of joins.

Answer 2

I am thinking the query should look like this:

SELECT c.Name, s.NameOfMedication, s.Stock,
       COALESCE(sl.OrderAmount, 0) as OrderAmount
FROM Stock s LEFT JOIN
     Clinic c
     ON s.ClinicID = c.ClinicID LEFT JOIN
     StockLog sl
     ON sl.StockID = s.StockID
WHERE s.Stock <= 5 ;

You want to keep all rows in Stock (subject to the WHERE condition). So think: "make Stock the first table in the FROM and use LEFT JOIN for all the other tables."