query 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)

query issue

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2013-09-19 : 02:38:52

Hi

I have data like

order_number PONumber
1 NULL_Value
1 NULL_Value
2 AAA
2 NULL_Value
3 AAA
3 AAA

I would like to display only orders which have only NULL values.
So the results should be only order 1 because order two contants one 'NULL_value' and order 3 not contain.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-19 : 03:01:10

My Guess Is ,

select Distinct Order_number from tablename where PoNumber = 'Null_Value' and Order_Number = 1

veeranjaneyulu

Dineshkumar89
Starting Member

2 Posts

Posted - 2013-09-19 : 03:05:20

Declare @table table (order_number int, PONumber varchar(50))
insert into @table
SELECT 1, 'NULL_Value' union ALL
SELECT 1, 'NULL_Value' union ALL
SELECT 2, 'AAA' union ALL
SELECT 2, 'NULL_Value' union ALL
SELECT 3, 'AAA' union ALL
SELECT 3, 'AAA'

SELECT * from @table
where PONumber LIKE '%NULL_VALUE%'

Regards
dineshkumar

Dineshkumar89
Starting Member

2 Posts

Posted - 2013-09-19 : 03:07:47

here it is,

Declare @table table (order_number int, PONumber varchar(50))
insert into @table
SELECT 1, 'NULL_Value' union ALL
SELECT 1, 'NULL_Value' union ALL
SELECT 2, 'AAA' union ALL
SELECT 2, 'NULL_Value' union ALL
SELECT 3, 'AAA' union ALL
SELECT 3, 'AAA'

select * from @table a
where NOT EXISTS
(
SELECT * from @table b
where a.order_number = b.order_number
and PONumber not LIKE '%NULL_VALUE%'
)

Regards
dineshkumar

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-19 : 03:15:53

You can get the order numbers which has NO PONumber by using following query...........
SELECT order_number
FROM @table
group by order_number
having count(*) = count(CASE WHEN PONumber = 'NULL_Value' then order_number END)

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-19 : 03:34:44

quote:
Originally posted by VeeranjaneyuluAnnapureddy

My Guess Is ,

select Distinct Order_number from tablename where PoNumber = 'Null_Value' and Order_Number = 1

veeranjaneyulu

By looking at sample data you can do the above where condition...
For actual data how OP will get to know the order_number which has NO PONumber?

--
Chandu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-19 : 04:29:52

SELECT Order_Number
FROM dbo.Table1
GROUP BY Order_Number
HAVING MIN(PONumbe) IS NULL

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-19 : 04:29:53

SELECT Order_Number
FROM dbo.Table1
GROUP BY Order_Number
HAVING MIN(PONumbe) IS NULL

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-19 : 09:11:31

SELECT Top(1) Order_Number
FROM Temp1
GROUP BY Order_Number
HAVING MIN(PONumber) IS NOT NULL

veeranjaneyulu

kwacz23
Starting Member

44 Posts

Posted - 2013-09-19 : 10:25:24

thanks for your all reply.

However I still have issue

because One one order contains NULl_values and poNumber I do not want to any line from this order.
I would expect all orders which only have NULL ( 1 or 2 or 3 so on....)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources