| Author |
 Topic  |
|
|
Daniel.V
Starting Member
Germany
2 Posts |
 Posted - 11/29/2012 : 17:09:52
|
Hello,
I have a special inner join question:
I have two Tabels:
Warehouse
WARHOUSE|LOCATION|ITEM|QUANTITY
000 |1 |XY |2
000 |1 |XY |1
000 |2 |XY |1
010 | |XX |2
Warhouse Config
WARHOUSE|LOCATION|KIND
000 |1 |BULK
000 |2 |RECEIVE
I need a Statement to get a Sum of all Items of the Warehouse (group by Items). But only with Items of the BULK location and the Items without locations.
The result has to be:
ITEM|SUM(QUANTITY)
XY |3
XX |2
Can I create for this one Statement?
Thanks,
Daniel |
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 11/29/2012 : 17:35:07
|
this seems to work:
declare @Warehouse table
(WARHOUSE char(3), LOCATION int, ITEM char(2),QUANTITY int)
insert @warehouse
select '000' ,1 ,'XY' ,2 union all
select '000' ,1 ,'XY' ,1 union all
select '000' ,2 ,'XY' ,1 union all
select '010' ,null ,'XX' ,2
declare @WarehouseConfig table
(WARHOUSE char(3), LOCATION int, KIND varchar(12))
insert @WarehouseConfig
select '000' ,1 ,'BULK' union all
select '000' ,2 ,'RECEIVE'
select w.item
,sum(quantity) [sum(Quantity)]
from @warehouse w
left outer join @warehouseConfig c
on c.warhouse = w.warhouse
and c.location = w.location
where (c.kind = 'bulk' or c.warhouse is null)
group by w.item
order by 2 desc
OUTPUT:
item sum(Quantity)
---- -------------
XY 3
XX 2
Be One with the Optimizer
TG |
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/29/2012 : 19:42:09
|
quote:
Originally posted by TG
this seems to work:
declare @Warehouse table
(WARHOUSE char(3), LOCATION int, ITEM char(2),QUANTITY int)
insert @warehouse
select '000' ,1 ,'XY' ,2 union all
select '000' ,1 ,'XY' ,1 union all
select '000' ,2 ,'XY' ,1 union all
select '010' ,null ,'XX' ,2
declare @WarehouseConfig table
(WARHOUSE char(3), LOCATION int, KIND varchar(12))
insert @WarehouseConfig
select '000' ,1 ,'BULK' union all
select '000' ,2 ,'RECEIVE'
select w.item
,sum(quantity) [sum(Quantity)]
from @warehouse w
left outer join @warehouseConfig c
on c.warhouse = w.warhouse
and c.location = w.location
where (c.kind = 'bulk' or c.warhouse is null)
group by w.item
order by 2 desc
OUTPUT:
item sum(Quantity)
---- -------------
XY 3
XX 2
Be One with the Optimizer
TG
Shouldn't it be c.Location is null  |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 11/30/2012 : 00:49:33
|
declare @Warehouse table
(WARHOUSE char(3), LOCATION int, ITEM char(2),QUANTITY int)
insert @warehouse
select '000' ,1 ,'XY' ,2 union all
select '000' ,1 ,'XY' ,1 union all
select '000' ,2 ,'XY' ,1 union all
select '010' ,null ,'XX' ,2
declare @WarehouseConfig table
(WARHOUSE char(3), LOCATION int, KIND varchar(12))
insert @WarehouseConfig
select '000' ,1 ,'BULK' union all
select '000' ,2 ,'RECEIVE'
SELECT Item, SUM(quantity) total
FROM @Warehouse
WHERE LOCATION = (SELECT LOCATION FROM @WarehouseConfig WHERE KIND = 'BULK')
OR LOCATION IS NULL
GROUP BY ITEM
--
Chandu |
|
|
|
Daniel.V
Starting Member
Germany
2 Posts |
Posted - 11/30/2012 : 08:25:04
|
Thanks a lot. It works great!!!
Daniel |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 11/30/2012 : 11:25:39
|
quote:
Originally posted by sodeep
Shouldn't it be c.Location is null
Shouldn't matter. Because left outer join on both columns if location is null in the source table then the row will be excluded meaning warhouse will also be null for the join result.
Be One with the Optimizer
TG |
|
|
| |
Topic |
|
|
|
Special Inner Join Question
