Joins on multiple tables to create a view

Question

I want to create a view by joining three tables.

The schema is a bit complicated, and so for the sake of simplicity, I will mention only the tables and columns required for the problem.

There is a table sales with two columns:

SaleID(pkey),
Buyer,
Amount.

The second table is purchases with two columns:

PurchaseID(pkey),
Seller,
Amount.

I have a third table stockhistory which keeps track of the flow of materials. The schema of that table is:

Date,
PurchaseID(fkey to column PurchaseID, Purchases Table),
SalesID(fkey to column SalesID, Sales Table),
Amount(calculated amount inserted here).

Now, each of the stockhistory records corresponds to either a record of Purchases table or Sales table, but not both or none. I have a constraint for that purpose:

([salesid] IS NULL AND [purchaseid] IS NOT NULL OR [salesid] IS NOT NULL AND
[purchaseid] IS NULL)

Now, in the view StockReport, I am trying to pull all records from stockhistory table, and want to display the name of buyer or seller as is the case.

I have tried to write a SQL of the lines of:

SELECT StockHistory.date
     , StockHistory.purchaseid
     , StockHistory.salesid
     , Purchases.seller
     , Sales.buyer
WHERE StockHistory.purchaseid = Purchases.purchaseid 
   OR StockHistory.salesid = Sales.salesid

How can I do the same with LEFT JOIN?

Why not `FROM STOCKHistory LEFT JOIN sales on.... LEFT JOIN purchases on ...` This way you always have all records from stockhistory and only those related in either sales or purchases? Now if you impose limits on sales or purchases... those limits would have to be put on the JOIN as AND criteria to the join otherwise you break the left joins making them act like inner joins. The limiting criteria for a table on the right side of a left join must be put on the join not in the where clause or you negate the left join! — xQbert, May 11 '17 at 16:29

score 3 · Accepted Answer · answered May 11 '17 at 16:29

3

You can use following query

    SELECT 
        SH.date, 
        SH.purchaseid, 
        SH.salesid, 
        P.seller, 
        S.buyer
    FROM stockhistory SH
    LEFT JOIN Purchases P on P.PurchaseID=SH.PurchaseID
    LEFT JOIN Sales S on S.salesid=SH.salesid

answered May 11 '17 at 16:29

DhruvJoshi

17,041
6
41
60

I was about to post this as the answer myself. You beat me to it. Thank you! – Sabyasachi Mukherjee May 11 '17 at 16:30

Joins on multiple tables to create a view

1 Answers1