Special Inner Join Question - 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

General SQL Server Forums

New to SQL Server Programming

Special Inner Join Question

Author

Topic

Daniel.V
Starting Member

2 Posts

Posted - 2012-11-29 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-29 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-29 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-30 : 00:49:33

[code]
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
[/code]

--
Chandu

Daniel.V
Starting Member

2 Posts

Posted - 2012-11-30 : 08:25:04

Thanks a lot. It works great!!!

Daniel

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-30 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources