13,298,965 members (38,153 online)
Rate this:
See more:
Table 1
Purchased_Items

Contains :
Item_ID:Number
Item_Quantity:Number

Table 2
Sold_Items

Contains :
Item_ID:Number
Item_Quantity:Number

How can I make a summation between two tables in (Item_Quantity), without inner join , these is a case should be handled if an item located in table 1 but not in table 2 , the is a value should be shown and vice versa , which is in table 1

Posted 18-Sep-12 8:14am
besthms1.1K
digimanus 18-Sep-12 15:09pm

why not an inner join? Have you tried Cross Join?

Rate this:

## Solution 1

If you would like to get purchased items which are not sold, use this query:
```SELECT *
FROM Purchased_Items
WHERE NOT Item_ID IN (SELECT Item_ID FROM Sold_Items)```

If you would like to sum Item_Quantity for both tables:
```SELECT 'Sum Of Purchased_Items' As [Item Description], SUM(Item_Quantity) AS [Item Value]
FROM Purchased_tems
UNION ALL
SELECT 'Sum Of Sold_Items' As [Item Description], SUM(Item_Quantity) AS [Item Value]
FROM Purchased_tems```

Example output:

Item DescriptionItem Value
Sum Of Purchased_Items5,000.00
Sum Of Sold_Items2,000.00
v2

Top Experts
Last 24hrsThis month
 OriginalGriff 75 Dave Kreskowiak 60 Jörgen Andersson 50 ppolymorphe 45 haiduong87 45
 OriginalGriff 2,374 Jochen Arndt 1,255 Richard MacCutchan 1,238 ppolymorphe 1,207 Dave Kreskowiak 1,055