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 = 1
I've tried select itemnumber , warehouses from stuff where warehouses <> '1' and warehouses > '1' group by itemnumber , warehouses
But 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 =1
The 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.
select itemnumber
,warehouses
from stuff
where itemnumber in
(
select itemnumber
from stuff
where warehouses = 1
group by itemnumber
having count(*) = 0
)
group by itemnumber , warehouses
select
a.itemnumber,
a.warehouses
from
stuff a
where
a.itemnumber not in
(
select
b.itemnumber
from
stuff b
where
b.warehouses = '1'
)
group by
a.itemnumber,
a.warehouses
CODO ERGO SUM
Edited by - Michael Valentine Jones on 12/12/2005 13:35:25
quote:Originally posted by Michael Valentine Jones
This should do it:
select
a.itemnumber,
a.warehouses
from
stuff a
where
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!
Group by?
Posted - 12/12/2005 : 12:43:43
