Full joins and NULL fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Full joins and NULL fields

Author

Topic

felicity_taylor@hotmail.c
Starting Member

1 Post

Posted - 2008-07-04 : 20:21:19

OK Ill explain this as best as I can (i am hoping this is 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));

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-07-07 : 03:14:24

Formatting is best done by your front end....outside of the SQL Query anaylser. It's far simpler doing this sort of thing in vb.net or crystal reports.....you can suppress repeating rows easily.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources