CODEWITHSUNDEEP
sqlsql-servert-sqlnullisnull
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10247

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

Upvotes: 0

Views: 128

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

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

Upvotes: 5

Related Questions