Full joins and Null Fields

felicity taylor posted at 04-Jul-08 07:52

OK Ill explain this as best as I can (its easy)

I have two tables - I buy widgets in the hundreds but they are different sorts.  I sell widgets of the same kind in different numbers and kinds.

I want a purchases and sales report all in one

i.e

purchaseName       PurchaseNo      PurchasePrice      SaleName    SaleNo      SalePrice
Widget1                100                  100                         Widget1      100          200
Widget2                100                  200                         Widget2      50            150
                                                                                    Widget2      25            150
Widget3                100                  300
Widget4                200                  400                         Widget4      50            100

You can see from the above how I want to format my report, i have managed to display all the data as above - the only issue being that if I sell part of the item multiple times it duplicates the purchase column - I really need the purcahse colums to be blank, here is what I have - i do hope someone can help

SELECT purchase.purchaseitem, purchase.purchaseprice, purchase.purchasenumber, sales.saleitem, sales.saleprice, sales.salenumber
FROM purchase INNER JOIN sales ON purchase.purchaseitem=sales.saleitem;
UNION ALL
SELECT purchase.purchaseitem, purchase.purchaseprice, purchase.purchasenumber, sales.saleitem, sales.saleprice, sales.salenumber
FROM purchase LEFT JOIN sales ON purchase.purchaseitem=sales.saleitem
WHERE (((sales.saleitem) Is Null));
UNION ALL
SELECT purchase.purchaseitem, purchase.purchaseprice, purchase.purchasenumber, sales.saleitem, sales.saleprice, sales.salenumber
FROM purchase RIGHT JOIN sales ON purchase.purchaseitem = sales.saleitem
WHERE (((purchase.purchaseitem) Is Null));


Click here to sign in and reply. You could earn money via our $500 contest just for being helpful.
  Full joins and Null Fields - felicity taylor  04-Jul-08 07:52 7:52:10 AM
      use cursors and try this.... - Vasanthakumar D  05-Jul-08 04:49 4:49:09 AM
View Posts