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.
Author |
Topic |
gogetsome
Starting Member
10 Posts |
Posted - 2005-12-12 : 12:43:43
|
Hello, I need some help with a group by. I have a table which contains itemnumber and warehouses. I need to find which itemnumber by group that do not have a warehouses that = 1I've tried select itemnumber , warehouses from stuffwhere warehouses <> '1' and warehouses > '1'group by itemnumber , warehousesBut this just eliminates the itemnumber with warehouses that = 1 in the results.I believe that I need to first group the itemnumber and then determin which of these groups have an itemnumber with a warehouses that do not =1The problem that I face is that each itemnumber should have a wherehouse of 1 and many other warehouse if the part is also stocked in that warehouse. But they must have 1 for the system to work properly. Well, things happen and some of the itemnumber where the warehouse = 1 were changed and I need to find those which have changed or do not have the default warehouse which = 1.Sorry for the confusion if I'm not being very clear. |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-12 : 13:23:39
|
>>Sorry for the confusion if I'm not being very clear.Follow these instructions to avoid not being very clear:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxbut maybe something like this?select itemnumber ,warehouses from stuffwhere itemnumber in ( select itemnumber from stuff where warehouses = 1 group by itemnumber having count(*) = 0 )group by itemnumber , warehouses Be One with the OptimizerTG |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-12-12 : 13:34:26
|
This should do it:select a.itemnumber, a.warehousesfrom stuff awhere a.itemnumber not in ( select b.itemnumber from stuff b where b.warehouses = '1' )group by a.itemnumber, a.warehouses CODO ERGO SUM |
|
|
gogetsome
Starting Member
10 Posts |
Posted - 2005-12-12 : 14:55:37
|
quote: Originally posted by Michael Valentine Jones This should do it:select a.itemnumber, a.warehousesfrom stuff awhere a.itemnumber not in ( select b.itemnumber from stuff b where b.warehouses = '1' )group by a.itemnumber, a.warehouses CODO ERGO SUM
Thank you for taking the time to help with this situation. This solution was what I was looking for. Very clean approach! |
|
|
|
|
|