Questions How can I prevent all Null values from being returned in this TSQL?

I've got this TSQL:

SELECT Invd.Unit, M.MemberName, Invd.Description
FROM InvoiceDetail Invd
LEFT JOIN Members M on Invd.MemberNo = M.MemberNo
GROUP BY Invd.Unit, M.MemberName, Invd.Description

...which works well for 90% of what's returned (aside from being agonizingly slow), but approximately 10% of the returned records contain a Null value for either Invd.Unit or M.MemberName or both.

I only want records with no Nulls anywhere. What do I need to do to prevent records with any null values from being returned in the result set?

I don't want to use something like this:

SELECT IsNull(Invd.Unit, 'no Unit'), IsNull(M.MemberName, 'no Member Name'), Invd.Description
. . .

I just don't want those records at all; I just want unique/distinct combinations of those three fields (excluding nulls in any of them - the data is only useful where all three values exist).

Comments :
Tom H replied

If you're not actually calculating aggregate values then DISTINCT would be much clearer here than the GROUP BY, IMO.

No, there's no calculating going on; just a list of unique combinations of those three fields.


1 Answers :
Pரதீப் answered

Change your LEFT Join to INNER Join with Where condition

SELECT Invd.unit, 
       M.membername, 
       Invd.description 
FROM   invoicedetail Invd 
       INNER JOIN members M 
               ON Invd.memberno = M.memberno 
WHERE  Invd.unit IS NOT NULL 
       AND M.membername IS NOT NULL 
       AND Invd.description IS NOT NULL 
GROUP  BY Invd.unit, 
          M.membername, 
          Invd.description 

As mentioned in comments it is better to use Distinct instead of Group by which will make the intention clear. When there is no Aggregate in Select the Sql server is smart enough to convert the group by to distinct

demo replied
Where for GROUP BY ???;
I guess Group By is used here to remove duplicates;
B. Clay Shannon replied
@VR46: Claro que si.;
B. Clay Shannon replied
I thought a DISTINCT() could only be done on one column.;
TT. replied
@B.ClayShannon DISTINCT applies to rows, not individual columns.;