April 27, 2009 at 11:05 am
How about something like this?
Declare @Table Table (OrderNo int, [LineNo] int, Product varchar(50), Result varchar(50))
Insert Into @Table
Select 1,1, 'ABC', NULL UNION ALL
Select 1,2, 'XYZ', 'Requires Review' UNION ALL
Select 2,1, 'FooBar', NULL UNION ALL
Select 2,2, 'ID10t', NULL
Select * from @Table
where OrderNo Not IN
(Select OrderNo From @Table Where Result IS NOT NULL)
April 27, 2009 at 11:50 am
Here is another method:
create table #Orders (
OrderNum int,
LineNum int,
Product varchar(10),
Result varchar(25)
)
;
insert into #Orders
select 1,1,'ABC',null union all
select 1,2,'XYZ','Requires Review' union all
select 2,1,'Foobar',null union all
select 2,2,'Id10t',null
;
select * from #Orders;
with OrderResults(
OrderNum
) as (
select distinct
OrderNum
from
#Orders
where
Result is not null
)
select
o.OrderNum,
o.LineNum,
o.Product,
o.Result
from
#Orders o
left outer join OrderResults ors
on (o.OrderNum = ors.OrderNum)
where
ors.OrderNum is null;
drop table #Orders;
April 28, 2009 at 1:58 am
Hi Ken and Lynn
Thank you both for your responses. I'm kicking myself now over the answer, I can see clearly what you are doing with both of these methods, for the script I need to make I will be using Ken's method at this time, however the CTE method by Lynn may be of use for a future itteration of the application. Again thank you both for your time.
Regards
Timothy Merridew
April 30, 2009 at 4:13 pm
There is an easier way to do it. Using the COUNT(ColumnName) function counts only those rows that aren't null.
Here's an example:
create table #Orders (
OrderNum int,
LineNum int,
Product varchar(10),
Result varchar(25)
)
;
insert into #Orders
select 1,1,'ABC',null union all
select 1,2,'XYZ','Requires Review' union all
select 2,1,'Foobar',null union all
select 2,2,'Id10t',null union all
select 3,1, 'ABC', 'Not Null' union all
select 3,2, 'DEF', 'Not Null'
;
-- Get the orders where all Result is null
SELECT OrderNum
FROM #Orders
GROUP BY OrderNum
HAVING COUNT(Result) = 0
-- You can also get all of the orders that don't
-- have any nulls in Result
SELECT OrderNum
FROM #Orders
GROUP BY OrderNum
HAVING COUNT(*) - COUNT(Result) = 0
Todd Fifield
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply