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).
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.
LEFT Join to
INNER Join with
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
Group Byis used here to remove duplicates;