Hi,

I'm quite new to the SQL-programming world so please be patient if my questions seem far to simple for this forum.

I have a problem where I need to combine results from several tables into one single resultset

The main table, PRODUCTS, contains all productinformation.

I would like the final resultset to contain partinformation from PRODUCTS, stock information from STOCK, purchase order information from PURCHASEORDER, and sales order from SALESORDER.

The tables STOCK, PURCHASEORDER and SALESORDER can contains multiple values for each part. i.e. there might be 1 or more purchaseorders for partnum XXX. There might also be 1 or more salesorders registered for partnum XXX.

I would like the fetch the number of items ordered from PURCHASEORDER, the total number of items in STOCK and the total number of items in SALESORDERS so I can present the total number of items available for a particular.


I would like the result to look something like this, with one row per item:

PRODUCTS.partnum, PRODUCTS.partname, STOCK.totalperitem, PURCHASEORDER.totalperitem, SALESORDER.totalperitem

or even better:

PRODUCTS.partnum, PRODUCTS.partname, (STOCK.totalperitem + PURCHASEORDER.totalperitem - SALESORDER.totalperitem)

I hope my question makes sense. I am slightly familiar with basic SELECT-queries but I'm quite inexperienced when it comes to JOINS, UNIONS etc. The test I've done are returning faulty SUM-values for the final resultset when I add SUM-values from secondary, third, etc. columns.

Any help greatly appreciated. I would also like to know of any book recommendations for learning SQL.

The application I'm building is in Lotus Notes but I am querying a MS SQL Server.

/Hans Fokine