SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Special Inner Join Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Daniel.V
Starting Member

Germany
2 Posts

Posted - 11/29/2012 :  17:09:52  Show Profile  Reply with Quote
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
5914 Posts

Posted - 11/29/2012 :  17:35:07  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/29/2012 :  19:42:09  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 11/30/2012 :  00:49:33  Show Profile  Reply with Quote

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
Go to Top of Page

Daniel.V
Starting Member

Germany
2 Posts

Posted - 11/30/2012 :  08:25:04  Show Profile  Reply with Quote
Thanks a lot. It works great!!!

Daniel
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 11/30/2012 :  11:25:39  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000