where issue. - 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

SQL Server 2008 Forums

Transact-SQL (2008)

where issue.

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2013-09-16 : 05:38:41

Hi

I have data

order number PONumber
1 NULL
1 NULL
2 AAA
2 NULL
3 AAA
3 AAA

I would like to display only orders which have onlyNULL values.
My query below only show me those which have only null or only AAA
but I want to have only null

with a as (
select OrderNumber,ponumber, COUNT(OrderNumber) number_lines from Table

group by OrderNumber,ponumber
--having COUNT(*)<2
--order by OrderNumber
)

select COUNT(*) order_number_lines,OrderNumber from a

group by OrderNumber
having COUNT(OrderNumber)=1

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-16 : 05:51:44

with a as (
select OrderNumber,ponumber, COUNT(OrderNumber) number_lines from Temp123
Where isnull(PONumber ,'') = ''
group by OrderNumber,ponumber
)
select COUNT(*) order_number_lines,OrderNumber from a
group by OrderNumber
having COUNT(OrderNumber)=1

order_number_lines OrderNumber

1 1

1 2

veeranjaneyulu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-16 : 05:51:53

select order_number from table group by order_number having min(PONumber) is null and max(PONumber) is null

Madhivanan

Failing to plan is Planning to fail

kwacz23
Starting Member

44 Posts

Posted - 2013-09-16 : 06:02:30

Hi

@VeeranjaneyuluAnnapureddy this 'NULL' is not a text. It is null value.
Unfotrunately your query is not working

@madhivanan. It seems to be OK but one order is there which have AAA.
I am not sure why

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-16 : 06:27:51

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-20 : 08:23:05

select order_number from table group by order_number having (min(PONumber) is null and max(PONumber) is null) or (min(PONumber) ='AAA' and max(PONumber) ='AAA')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources