InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 08:13:35 GMT20http://www.sqlservercentral.com/Forums/Topic705079-338-1.aspxThere is an easier way to do it. Using the COUNT(ColumnName) function counts only those rows that aren't null.Here's an example:[code]create table #Orders ( OrderNum int, LineNum int, Product varchar(10), Result varchar(25));insert into #Ordersselect 1,1,'ABC',null union allselect 1,2,'XYZ','Requires Review' union allselect 2,1,'Foobar',null union allselect 2,2,'Id10t',null union allselect 3,1, 'ABC', 'Not Null' union allselect 3,2, 'DEF', 'Not Null';[/code] [code]-- Get the orders where all Result is nullSELECT OrderNumFROM #OrdersGROUP BY OrderNumHAVING COUNT(Result) = 0[/code][code]-- You can also get all of the orders that don't-- have any nulls in ResultSELECT OrderNumFROM #OrdersGROUP BY OrderNumHAVING COUNT(*) - COUNT(Result) = 0[/code]Todd FifieldThu, 30 Apr 2009 16:13:31 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic705079-338-1.aspxHi Ken and LynnThank 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.RegardsTimothy MerridewTue, 28 Apr 2009 01:57:59 GMTtimothy.merridewhttp://www.sqlservercentral.com/Forums/Topic705079-338-1.aspxHere is another method:[code]create table #Orders ( OrderNum int, LineNum int, Product varchar(10), Result varchar(25));insert into #Ordersselect 1,1,'ABC',null union allselect 1,2,'XYZ','Requires Review' union allselect 2,1,'Foobar',null union allselect 2,2,'Id10t',null;select * from #Orders;with OrderResults( OrderNum) as (select distinct OrderNumfrom #Orderswhere Result is not null)select o.OrderNum, o.LineNum, o.Product, o.Resultfrom #Orders o left outer join OrderResults ors on (o.OrderNum = ors.OrderNum)where ors.OrderNum is null;drop table #Orders;[/code]Mon, 27 Apr 2009 11:50:30 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic705079-338-1.aspxHow about something like this?[code]Declare @Table Table (OrderNo int, [LineNo] int, Product varchar(50), Result varchar(50))Insert Into @TableSelect 1,1, 'ABC', NULL UNION ALLSelect 1,2, 'XYZ', 'Requires Review' UNION ALLSelect 2,1, 'FooBar', NULL UNION ALLSelect 2,2, 'ID10t', NULL Select * from @Tablewhere OrderNo Not IN (Select OrderNo From @Table Where Result IS NOT NULL)[/code]Mon, 27 Apr 2009 11:05:40 GMTKenSimmonshttp://www.sqlservercentral.com/Forums/Topic705079-338-1.aspxHi everyone, I'm in the process of creating some tsql to do various lookups and selections on some order tables we have here. The current table I'm working against has the header and details in a single table (due to application design and other requirements). What I'm trying to do is work out the sql required for a selection of orders where the result column is Null for all order lines on that order. I'll give an example to help illustrate the problem.OrderNo LineNo Product Result1 1 ABC Null1 2 XYZ Requires review2 1 Foobar Null2 2 Id10t NullI need a script that can return all orders where all order lines for each OrderNo are Null in the Result column. I thought I had it with using a CTE and a count distinct, however checking the results I can see orders in my list where there is a row that has Requires review in it.Any guidance or help would be most appreciated.... Sorry I've been unable to product a table extraction, however the above should show a much simpler dataset.Mon, 27 Apr 2009 10:05:00 GMTtimothy.merridew